﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DHQGHN.DAL.BO.DHQG;
using System.Web.Mvc;
using DHQGHN.PM2.Models.DHQG;
using Newtonsoft.Json;
using System.Web.Script.Serialization;
using System.Data;
using DHQGHN.DAL.Models.DHQG;
using OfficeOpenXml.Style;
using System.Drawing;
using System.IO;
using ICSharpCode.SharpZipLib.Zip;
using ICSharpCode.SharpZipLib.Core;
using OfficeOpenXml;
//using VTUtils.Excel.Export;
using FlexCel.XlsAdapter;
using FlexCel.Core;
using FlexCel.Render;

namespace DHQGHN.PM2.Controllers
{
    public class KehoachnamController : BaseController
    {
        NamkehoachChitieuBO namchitieubo = new NamkehoachChitieuBO();
        NamKeHoachBO namkehoachbo = new NamKeHoachBO();
        MocKiemSoatBO mockiemsoatbo;
        History_CommentBO historycommnet;
        DonViBO donvibo = new DonViBO();
        ChiTieuBO chitieubo = new ChiTieuBO();
        DonViChiTieuBO donvichitieubo = new DonViChiTieuBO();
        KeHoachBO kehoachbo = new KeHoachBO();
        NhomDonViBO nhomdvbo = new NhomDonViBO();
        KehoachThuchienBO kehoachthuchienbo = new KehoachThuchienBO();
        CommentBO commentbo;
        ShareFileBO sharefilebo;
        PhanQuyenVanBanBO phaquyenbo;

        bool isLandScape = false;
        double landscapeSize = 203;
        double portraitSize = 140;
        double dvtColumnWidth = 12;
        double dvColumnWidth = 6;
        decimal topMargin = 0.2m;
        decimal bottomMargin = 0.2m;
        decimal leftMargin = 0.5m;
        decimal rightMargin = 0.2m;
        int startRow = 4;
        int startCol = 1;
        //
        // GET: /Kehoachnam/
        public ActionResult Index()
        {
            ViewBag.ActiveMenus = ViewBag.ActiveMenus == null ? new System.Collections.Hashtable() : ViewBag.ActiveMenus;
            ViewBag.ActiveMenus[284] = "active";
            ViewBag.ActiveMenus[285] = "active";
            ViewBag.ActiveMenus[286] = "active";
            ViewBag.ActiveMenus[302] = "active";

            return View();
        }

        //phunx1, 20160519: ham xu ly active menu vua click va lay menupath tren dau form
        private void activeMenu(string strInputMenuUrl)
        {
            ViewBag.ActiveMenus = ViewBag.ActiveMenus == null ? new System.Collections.Hashtable() : ViewBag.ActiveMenus;

            List<DHQGHN.PM2.Models.MenuModel> listMenu = Session["MenuList"] as List<DHQGHN.PM2.Models.MenuModel>;
            if (listMenu.Count > 0)
            {
                for (int i = 0; i < listMenu.Count; i++)
                {
                    if (listMenu[i].MenuUrl.ToUpper().Equals(strInputMenuUrl.ToUpper()))
                    {
                        ViewBag.ActiveMenus[listMenu[i].ParentMenuId] = "active";
                        ViewBag.ActiveMenus[listMenu[i].MenuId] = "active";
                        ViewBag.CurrentMenuName = listMenu[i].MenuName;
                    }
                }
            }
        }

        public ActionResult Lapkehoachdinhhuong()
        {
            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            ViewBag.MaDV = user.listDonVi[0].MaDonVi;
            ViewBag.DonviID = user.DON_VI_ID;

            return PartialView("~/Views/Kehoachnam/KehoachdinhhuongPartial.cshtml");
        }
        public ActionResult Lapkehoach()
        {
            return PartialView("~/Views/Kehoachnam/LapkehoachPartial1.cshtml");
        }
        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public int InsertUpdateNamkehoach(DAL.Models.DHQG.NamKeHoach namkh)
        {
            namkh.STATUS_DATE = DateTime.Today;
            LogStream.Write<DAL.Models.DHQG.NamKeHoach>(GetAccountLogin(), CommonData.AppName, "KehoachnameController",
                "Thêm mới năm kế hoạch - InsertUpdateNamkehoach", namkh, Helper.KH_DHQGHN_CODE);
            return namkehoachbo.Save(namkh);
        }
        public bool DeleteNamkehoach(int id)
        {
            LogStream.Write(GetAccountLogin(), CommonData.AppName, "KehoachnameController",
               "Xóa năm kế hoạch - DeleteNamkehoach", "{ ID: " + id + " }", Helper.KH_DHQGHN_CODE);
            return namkehoachbo.Delete(id);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetNamkehoachbyID(int id)
        {
            DAL.Models.DHQG.NamKeHoach name = new DAL.Models.DHQG.NamKeHoach();
            name = namkehoachbo.GetById(id);
            return Json(name, JsonRequestBehavior.AllowGet);
        }
        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public int InsertMockiemsoat(DAL.Models.DHQG.MocKiemSoat mocks)
        {
            DAL.Models.DHQG.NamKeHoach nam = new DAL.Models.DHQG.NamKeHoach();
            nam = namkehoachbo.GetById(mocks.NAM_KE_HOACH_ID);
            if (nam.STATUS_ID == 0)
            {
                mockiemsoatbo = new MocKiemSoatBO();
                LogStream.Write<MocKiemSoat>(GetAccountLogin(), CommonData.AppName,
                    "KehoachnamController", "Thêm mới mốc kiểm soát - InsertMockiemsoat", mocks, Helper.KH_DHQGHN_CODE);
                return mockiemsoatbo.Save(mocks);
            }
            else
            {
                return -1;
            }
        }
        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public int UpdateMockiemsoat(int mockiemsoatid)
        {
            mockiemsoatbo = new MocKiemSoatBO();
            DAL.Models.DHQG.MocKiemSoat moc = new DAL.Models.DHQG.MocKiemSoat();
            moc = mockiemsoatbo.GetbyID(mockiemsoatid);
            moc.IS_ACTIVE = 1;
            MocKiemSoatBO nss = new MocKiemSoatBO();
            LogStream.Write<MocKiemSoat>(GetAccountLogin(), CommonData.AppName,
                    "KehoachnamController", "Cập nhật mốc kiểm soát - UpdateMockiemsoat", moc, Helper.KH_DHQGHN_CODE);
            return nss.Save(moc);


        }
        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public int InsertKHTHbyOption(int namkhid, int chitieu, int donvi, int mockiemsoat, int flag, string giatri)
        {
            mockiemsoatbo = new MocKiemSoatBO();
            DAL.Models.DHQG.MocKiemSoat moc = new DAL.Models.DHQG.MocKiemSoat();
            moc = mockiemsoatbo.GetMocKSIsActive(namkhid);
            int k = -1;
            if (moc != null) {
                if (moc.MOC_KIEM_SOAT_ID != mockiemsoat) {
                    flag = 0;
                }
                k = kehoachthuchienbo.InsertKHTHbyOption(namkhid, chitieu, donvi, mockiemsoat, flag, giatri);
            }
            return k;
        }
        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public int InsertMockiemsoatDefault(int namkhid, int nam)
        {
            DAL.Models.DHQG.NamKeHoach namkh = namkehoachbo.GetById(namkhid);
            if (namkh.STATUS_ID == 0)
            {
                mockiemsoatbo = new MocKiemSoatBO();
                DAL.Models.DHQG.MocKiemSoat a = new DAL.Models.DHQG.MocKiemSoat();
                a.NAM_KE_HOACH_ID = namkhid;
                a.MOC_KIEM_SOAT_ID = 0;
                a.MOC_KIEM_SOAT = "Đầu năm " + nam;
                a.IS_ACTIVE = 0;
                string th = "01/01/" + nam;
                a.THOI_HAN = DateTime.Parse(th);
                mockiemsoatbo.Save(a);

                LogStream.Write<DAL.Models.DHQG.MocKiemSoat>(GetAccountLogin(), CommonData.AppName,
                    "KehoachnamController", "InsertMockiemsoatDefault", a, Helper.KH_DHQGHN_CODE);

                DAL.Models.DHQG.MocKiemSoat b = new DAL.Models.DHQG.MocKiemSoat();
                b.NAM_KE_HOACH_ID = namkhid;
                b.MOC_KIEM_SOAT_ID = 0;
                b.MOC_KIEM_SOAT = "QUÝ I năm " + nam;
                b.IS_ACTIVE = 0;
                int thang2 = DateTime.DaysInMonth(nam, 03);
                string th1 = "03/" + thang2 + "/" + nam;
                b.THOI_HAN = DateTime.Parse(th1);
                mockiemsoatbo.Save(b);

                LogStream.Write<DAL.Models.DHQG.MocKiemSoat>(GetAccountLogin(), CommonData.AppName,
                    "KehoachnamController", "InsertMockiemsoatDefault", b, Helper.KH_DHQGHN_CODE);

                DAL.Models.DHQG.MocKiemSoat c = new DAL.Models.DHQG.MocKiemSoat();
                c.NAM_KE_HOACH_ID = namkhid;
                c.MOC_KIEM_SOAT_ID = 0;
                c.MOC_KIEM_SOAT = "Giữa năm " + nam;
                c.IS_ACTIVE = 0;
                int thang3 = DateTime.DaysInMonth(nam, 06);
                string th2 = "06/" + thang3 + "/" + nam;
                c.THOI_HAN = DateTime.Parse(th2);
                mockiemsoatbo.Save(c);

                LogStream.Write<DAL.Models.DHQG.MocKiemSoat>(GetAccountLogin(), CommonData.AppName,
                    "KehoachnamController", "InsertMockiemsoatDefault", c, Helper.KH_DHQGHN_CODE);

                DAL.Models.DHQG.MocKiemSoat d = new DAL.Models.DHQG.MocKiemSoat();
                d.NAM_KE_HOACH_ID = namkhid;
                d.MOC_KIEM_SOAT_ID = 0;
                d.IS_ACTIVE = 0;
                d.MOC_KIEM_SOAT = "QUÝ III năm " + nam;
                int thang4 = DateTime.DaysInMonth(nam, 09);
                string th3 = "09/" + thang4 + "/" + nam;
                d.THOI_HAN = DateTime.Parse(th3);
                mockiemsoatbo.Save(d);

                LogStream.Write<DAL.Models.DHQG.MocKiemSoat>(GetAccountLogin(), CommonData.AppName,
                   "KehoachnamController", "InsertMockiemsoatDefault", d, Helper.KH_DHQGHN_CODE);

                DAL.Models.DHQG.MocKiemSoat e = new DAL.Models.DHQG.MocKiemSoat();
                e.NAM_KE_HOACH_ID = namkhid;
                e.MOC_KIEM_SOAT_ID = 0;
                e.IS_ACTIVE = 0;
                e.MOC_KIEM_SOAT = "Cuối năm " + nam;
                int thang5 = DateTime.DaysInMonth(nam, 12);
                string th4 = "12/" + thang5 + "/" + nam;
                e.THOI_HAN = DateTime.Parse(th4);
                mockiemsoatbo.Save(e);
                return 1;
            }
            else
            {
                return -1;
            }
        }
        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public int DLETEMockiemsoat(int id)
        {
            mockiemsoatbo = new MocKiemSoatBO();
            mockiemsoatbo.Delete(id);
            LogStream.Write(GetAccountLogin(), CommonData.AppName,
                "KehoachnamController", "Xóa mốc kiểm soát - DLETEMockiemsoat", "{ID: " + id + "}", Helper.KH_DHQGHN_CODE);
            return 1;
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetMockiemsoatbyID(int id)
        {
            mockiemsoatbo = new MocKiemSoatBO();
            List<DAL.Models.DHQG.MocKiemSoat> ad = mockiemsoatbo.GetbyNamkehoach(id);
            return Json(ad, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetMockiemsoatID(int id)
        {
            mockiemsoatbo = new MocKiemSoatBO();
            DAL.Models.DHQG.MocKiemSoat ad = mockiemsoatbo.GetbyID(id);
            return Json(ad, JsonRequestBehavior.AllowGet);
        }
        public List<SelectListItem> Getallyear()
        {
            List<SelectListItem> allyear = new List<SelectListItem>();
            int year = DateTime.Now.Year;
            for (int i = year + 1; i <= 2010; i--)
            {
                allyear.Add(new SelectListItem
                {
                    Value = i.ToString(),
                    Text = i.ToString()
                });
            }
            return allyear;
        }
        //Mục 3.2.3 Moo dữ liệu năm
        public ActionResult Khoitaokehoach()
        {
            this.activeMenu("/Kehoachnam/Khoitaokehoach");

            ViewBag.Getallyear = Getallyear();
            return PartialView("~/Views/Kehoachnam/KhoitaokehoachPartial1.cshtml");
        }
        //Mục 3.3.1 Lập kế hoạch định hướng
        public ActionResult Kybaocao()
        {
            this.activeMenu("/Kehoachnam/Kybaocao");

            ViewBag.Getallyear = Getallyear();
            return PartialView("~/Views/Kehoachnam/KybaocaoPartial1.cshtml");
        }
        public ActionResult Lapkehoachdinhhuongchuan()
        {
            this.activeMenu("/Kehoachnam/Lapkehoachdinhhuongchuan");

            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            if (user != null)
            {
                ViewBag.MaDV = user.listDonVi.Count > 0 ? user.listDonVi[0].MaDonVi : string.Empty;
                if (user.listDonVi.Count > 0 && user.listDonVi[0].MaDonVi == "KHTC")
                {
                    ViewBag.DonviID = "0";
                }
                else
                {
                    ViewBag.DonviID = user.listDonVi.Count > 0 ? user.listDonVi[0].DonViId : 0;
                }
                //ViewBag.DonviID = "1";
                //ViewBag.MaDV = "HHH";
            }
            return PartialView("~/Views/Kehoachnam/LapkehoachdinhhuongPartial1.cshtml");
        }
        //Mục 3.3.2 Phản hồi chỉ tiêu định hướng
        public ActionResult Kehoachdinhhuongnam()
        {
            return PartialView("~/Views/Kehoachnam/KehoachdinhhuongnamPartial1.cshtml");
        }
        //Mục 3.3.5 Điều chỉnh kế hoạch giữa năm

        public ActionResult Dieuchinhkehoach()
        {
            this.activeMenu("/Kehoachnam/Dieuchinhkehoach");
            List<SelectListItem> listDV = new List<SelectListItem>();
            ViewBag.ListDonVi = listDV;
            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            if (user != null)
            {
                ViewBag.MaDV = user.listDonVi[0].MaDonVi;
                if (user.listDonVi[0].MaDonVi == "KHTC")
                {
                    ViewBag.DonviID = "0";
                }
                else
                {
                    ViewBag.DonviID = user.listDonVi[0].DonViId.ToString();
                }
            }
            return PartialView("~/Views/Kehoachnam/DieuchinhkehoachPartial1.cshtml");
        }

        //phunx1, 20160518: metho dung cho menu "Ban hanh khung chi tieu" su dung cho Ban KHTC
        public ActionResult Dieuchinhkehoach_BanKHTC()
        {
            this.activeMenu("/Kehoachnam/Dieuchinhkehoach_BanKHTC");
            DonViBO bo = new DonViBO();
            var tempListDV = bo.GetAll();
            List<SelectListItem> listDV = new List<SelectListItem>();
            foreach (var item in tempListDV.OrderBy(x => x.MaDonVi))
            {
                listDV.Add(new SelectListItem() { Value = item.DonViId.ToString(), Text = item.TenDonVi });
            }
            ViewBag.ListDonVi = listDV;
            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            if (user != null)
            {
                ViewBag.MaDV = user.listDonVi[0].MaDonVi;
                if (user.listDonVi[0].MaDonVi == "KHTC")
                {
                    ViewBag.DonviID = "0";
                }
                else
                {
                    ViewBag.DonviID = user.listDonVi[0].DonViId;
                }
            }
            return PartialView("~/Views/Kehoachnam/DieuchinhkehoachPartial1.cshtml");
        }
        public ActionResult Dieuchinhkehoachgiuanam()
        {
            this.activeMenu("/Kehoachnam/Dieuchinhkehoachgiuanam");

            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            if (user != null)
            {
                ViewBag.MaDV = user.listDonVi[0].MaDonVi;
                if (user.listDonVi[0].MaDonVi == "KHTC")
                {
                    ViewBag.DonviID = "0";
                }
                else
                {
                    ViewBag.DonviID = user.listDonVi[0].DonViId;
                }
            }
            return PartialView("~/Views/Kehoachnam/DieuchinhkehoachgiuanamPartial1.cshtml");
        }

        public ActionResult Dieuchinhkehoachgiuanam_BanKHTC()
        {
            this.activeMenu("/Kehoachnam/Dieuchinhkehoachgiuanam_BanKHTC");

            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            if (user != null)
            {
                ViewBag.MaDV = user.listDonVi[0].MaDonVi;
                if (user.listDonVi[0].MaDonVi == "KHTC")
                {
                    ViewBag.DonviID = "0";
                }
                else
                {
                    ViewBag.DonviID = user.listDonVi[0].DonViId;
                }
            }
            return PartialView("~/Views/Kehoachnam/DieuchinhkehoachgiuanamPartial1.cshtml");
        }

        public ActionResult NamChitieu()
        {
            this.activeMenu("/Kehoachnam/NamChitieu");

            return PartialView("~/Views/Kehoachnam/NamChitieuPartial1.cshtml");
        }
        //KHỞI TẠO NĂM KẾ HOẠCH
        public ActionResult KhoitaoNamdinhhuong()
        {
            this.activeMenu("/Kehoachnam/KhoitaoNamdinhhuong");

            return PartialView("~/Views/Kehoachnam/KhoitaoNamdinhhuongPartial1.cshtml");
        }
        //GÁN CHỈ TIÊU CHO ĐƠN VỊ
        public ActionResult GanchitieuDonVi()
        {
            this.activeMenu("/Kehoachnam/GanchitieuDonVi");

            return PartialView("~/Views/Kehoachnam/GanchitieuDonViPartial1.cshtml");
        }
        public ActionResult GanchitieuRiengDonVi()
        {
            this.activeMenu("/Kehoachnam/GanchitieuRiengDonVi");

            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            if (user != null)
            {
                ViewBag.DonviID = user.listDonVi[0].DonViId;
                ViewBag.TenDV = user.listDonVi[0].TenDonVi;
            }
            return PartialView("~/Views/Kehoachnam/GanchitieuRiengDonViPartial1.cshtml");
        }
        public ActionResult Phanhoi()
        {
            this.activeMenu("/Kehoachnam/Phanhoi");

            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            if (user != null)
            {
                ViewBag.DonviID = user.listDonVi[0].DonViId;
                ViewBag.TenDV = user.listDonVi[0].TenDonVi;
                ViewBag.IsAdmin = user.IS_FIX;
            }
            return PartialView("~/Views/Kehoachnam/PhanhoiDinhhuongPartial1.cshtml");
        }
        public ActionResult ThuchienKehoach()
        {
            this.activeMenu("/Kehoachnam/ThuchienKehoach");

            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            if (user != null)
            {
                ViewBag.DonviID = user.listDonVi[0].DonViId;
                ViewBag.TenDV = user.listDonVi[0].TenDonVi;
                ViewBag.IsAdmin = user.IS_FIX;
            }
            return PartialView("~/Views/Kehoachnam/ThuchienKehoachPartial1.cshtml");
        }
        public ActionResult PhanhoiGiuanam()
        {
            this.activeMenu("/Kehoachnam/Phanhoigiuanam");

            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            if (user != null)
            {
                ViewBag.DonviID = user.listDonVi[0].DonViId;
                ViewBag.TenDV = user.listDonVi[0].TenDonVi;
                ViewBag.IsAdmin = user.IS_ADMIN;
            }
            return PartialView("~/Views/Kehoachnam/PhanhoiGiuanamPartial1.cshtml");
        }


        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetallChitieu()
        {
            List<DAL.Models.DHQG.ChitieuNew> chitieu = new List<DAL.Models.DHQG.ChitieuNew>();
            chitieu = chitieubo.Getall();
            return Json(chitieu, JsonRequestBehavior.AllowGet);
        }
        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public int SaveNamChitieu(int namkehoachid, string[] listchitieu)
        {
            List<DAL.Models.DHQG.NamkehoachChitieu> lst = new List<DAL.Models.DHQG.NamkehoachChitieu>();
            for (int i = 0; i < listchitieu.Length; i++)
            {
                DAL.Models.DHQG.NamkehoachChitieu new1 = new DAL.Models.DHQG.NamkehoachChitieu();
                new1.ID = 0;
                new1.NAM_KE_HOACH_ID = namkehoachid;
                new1.CHI_TIEU_ID = int.Parse(listchitieu[i]);
                lst.Add(new1);
            }
            LogStream.Write<List<DAL.Models.DHQG.NamkehoachChitieu>>(GetAccountLogin(), CommonData.AppName,
                    "KehoachnamController", "Lưu năm kế hoạch chỉ tiêu - SaveNamChitieu", lst, Helper.KH_DHQGHN_CODE);
            namchitieubo.Save(lst);
            return 1;
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult Getbanchuachot(int namkh, int status)
        {
            int dvid = 0;
            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            if (user != null)
            {
                dvid = user.listDonVi[0].DonViId;
            }
            var kq = kehoachbo.Getbanchuachot(namkh, dvid, status);
            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetallNamkehoach()
        {
            List<DAL.Models.DHQG.NamKeHoach> lst = new List<DAL.Models.DHQG.NamKeHoach>();
            lst = namkehoachbo.GetAllNamkehoach();
            return Json(lst, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetallChitieubynamchitieu(int namchitieuid, int madonvi)
        {
            List<DAL.Models.DHQG.ChitieuNew> lst = new List<DAL.Models.DHQG.ChitieuNew>();
            lst = chitieubo.Getalllistbynamchitieu(namchitieuid, madonvi);
            return Json(lst, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetallChitieubynamchitieuGiuanam(int namchitieuid, int madonvi)
        {
            List<DAL.Models.DHQG.ChitieuNew> lst = new List<DAL.Models.DHQG.ChitieuNew>();
            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            lst = chitieubo.GetalllistbynamchitieubydonviGiuanam(namchitieuid, madonvi, user.NGUOI_DUNG_ID);
            return Json(lst, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult Getallchitieurieng()
        {
            int dvid = 0;
            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            if (user != null)
            {
                dvid = user.listDonVi[0].DonViId;
            }
            List<DAL.Models.DHQG.ChitieuNew> lst = new List<DAL.Models.DHQG.ChitieuNew>();
            lst = chitieubo.Getallchitieurieng(dvid);
            return Json(lst, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetallChitieubynamchitieubydonvi(int namchitieuid, int madonvi)
        {
            List<DAL.Models.DHQG.ChitieuNew> lst = new List<DAL.Models.DHQG.ChitieuNew>();
            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            lst = chitieubo.Getalllistbynamchitieubydonvi(namchitieuid, madonvi, user.NGUOI_DUNG_ID);
            return Json(lst, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetDonViByNhom(bool status)
        {
            List<DAL.Models.DHQG.DonVi> lst = new List<DAL.Models.DHQG.DonVi>();
            lst = donvibo.GetDonViByType(status).OrderBy(x => x.MaDonVi).ThenBy(x => x.TenDonVi).ToList();
            return Json(lst, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetAllDonViByNhomID(int nhomdvid)
        {
            List<DAL.Models.DHQG.DonVi> lst = new List<DAL.Models.DHQG.DonVi>();
            //phunx1, 20160420: thay doi method lay cac nhom co ban theo nhomID
            //lst = donvibo.GetAllDonViByNhom(nhomdvid);
            lst = donvibo.GetAllDonViCoBanByNhom(nhomdvid);
            return Json(lst, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetAllDonViByNhomIDnew(int nhomdvid)
        {
            List<DAL.Models.DHQG.DonVi> lst = new List<DAL.Models.DHQG.DonVi>();
            //phunx1, 20160420: thay doi method lay cac nhom co ban theo nhomID
            lst = donvibo.GetAllDonViByNhom(nhomdvid);
            //lst = donvibo.GetAllDonViCoBanByNhom(nhomdvid);
            return Json(lst, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetDonViID(int donviid)
        {
            DAL.Models.DHQG.DonVi lst = new DAL.Models.DHQG.DonVi();
            //phunx1, 20160420: thay doi method lay cac nhom co ban theo nhomID
            //lst = donvibo.GetAllDonViByNhom(nhomdvid);
            lst = donvibo.GetById(donviid);
            return Json(lst, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult Getkehoachthuchien(int namkh, int status, int mockiemsoat, int flag)
        {
            if (flag == 1)
            {
                mockiemsoat = mockiemsoat - 1;
                status = 101;
            }
            List<DAL.Models.DHQG.KeHoachThucHien> lst = new List<DAL.Models.DHQG.KeHoachThucHien>();
            lst = kehoachthuchienbo.Getallbynamkh(namkh, status, mockiemsoat);
            return Json(lst, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult Getchitieuriengbydv(int namkh)
        {
            int dvid = 0;
            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            if (user != null)
            {
                dvid = user.listDonVi[0].DonViId;
            }
            var lst = donvichitieubo.GetChitieuriengbydv(namkh, dvid);
            return Json(lst, JsonRequestBehavior.AllowGet);
        }
        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public JsonResult SaveDonViChiTieu(List<DAL.Models.DHQG.DonViChiTieu> modelObj, List<DAL.Models.DHQG.KeHoach> lstkh, int flag, int statusid, int nhomchitieu, int nhomdonvi, int chot)
        {
            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            string jsonData = "";
            if (modelObj != null)
            {
                if (flag != 5)
                {
                    jsonData = "{NamKH: " + modelObj[0].NAM_KE_HOACH_ID + ", DonVi: " + modelObj[0].DON_VI_ID + ", FLAG: " + flag + "}";
                    donvichitieubo.DeleteDonViChiTieuByNKH(modelObj[0].NAM_KE_HOACH_ID, modelObj[0].DON_VI_ID, flag);
                }
                else
                {
                    jsonData = "{NamKH: " + modelObj[0].NAM_KE_HOACH_ID + ", NhomDonVi: " + nhomdonvi + "}";
                    donvichitieubo.DeleteDonViChiTieuRiengByNKH(modelObj[0].NAM_KE_HOACH_ID, nhomdonvi);
                }
                LogStream.Write(GetAccountLogin(), CommonData.AppName,
                    "KehoachnamController", jsonData, "Lưu Dơn vị chỉ tiêu - SaveDonViChiTieu", Helper.KH_DHQGHN_CODE);

                LogStream.Write<List<DAL.Models.DHQG.DonViChiTieu>>(GetAccountLogin(), CommonData.AppName,
                    "KehoachnamController", "Lưu Dơn vị chỉ tiêu - SaveDonViChiTieu", modelObj, Helper.KH_DHQGHN_CODE);
                donvichitieubo.Save(modelObj);
            }
            if (lstkh != null && lstkh[0].CHI_TIEU_ID != -1000)
            {
                if (chot == 1)
                {
                    kehoachbo.DeleteKehoachbyNKH(lstkh[0].NAM_KE_HOACH_ID, statusid, nhomchitieu, nhomdonvi);
                    kehoachbo.Save(lstkh);

                    LogStream.Write<List<DAL.Models.DHQG.KeHoach>>(GetAccountLogin(), CommonData.AppName,
                    "KehoachnamController", "Lưu Dơn vị chỉ tiêu - SaveDonViChiTieu", lstkh, Helper.KH_DHQGHN_CODE);

                    jsonData = "{NamKHId: " + lstkh[0].NAM_KE_HOACH_ID + ", StatusId: " + statusid + ", NhomChiTieu: " +
                        nhomchitieu + ", nhomdonvi: " + nhomdonvi + "}";
                    LogStream.Write(GetAccountLogin(), CommonData.AppName,
                        "KehoachnamController", jsonData, "DeleteKehoachbyNKH", Helper.KH_DHQGHN_CODE);

                    int a = namkehoachbo.CheckChot(lstkh[0].NAM_KE_HOACH_ID, statusid, user.listDonVi[0].DonViId);
                    if (a == 1 && user.listDonVi[0].DonViId != 30)
                    {
                        return Json("Bạn chưa lập định hướng cho tất cả các đơn vị !");
                    }
                    else
                    {
                        return Json("Bạn đã gửi bản định hướng thành công !");
                    }
                }
                else
                {
                    kehoachbo.DeleteKehoachbyNKH(lstkh[0].NAM_KE_HOACH_ID, statusid, nhomchitieu, nhomdonvi);
                    kehoachbo.Save(lstkh);

                    LogStream.Write<List<DAL.Models.DHQG.KeHoach>>(GetAccountLogin(), CommonData.AppName,
                    "KehoachnamController", "Lưu Dơn vị chỉ tiêu - SaveDonViChiTieu", lstkh, Helper.KH_DHQGHN_CODE);

                    jsonData = "{NamKHId: " + lstkh[0].NAM_KE_HOACH_ID + ", StatusId: " + statusid + ", NhomChiTieu: " +
                        nhomchitieu + ", nhomdonvi: " + nhomdonvi + "}";
                    LogStream.Write(GetAccountLogin(), CommonData.AppName,
                        "KehoachnamController", jsonData, "DeleteKehoachbyNKH", Helper.KH_DHQGHN_CODE);

                    return Json("Bạn đã tạo thành công !");
                }

            }

            if (lstkh != null && lstkh.Count() == 1 && lstkh[0].CHI_TIEU_ID == -1000)
            {
                if (chot == 1)
                {
                    int a = namkehoachbo.CheckChot(lstkh[0].NAM_KE_HOACH_ID, statusid, user.listDonVi[0].DonViId);
                    if (a == 1 && user.listDonVi[0].DonViId != 30)
                    {
                        return Json("Bạn chưa lập định hướng cho tất cả các đơn vị !");
                    }
                    else
                    {
                        return Json("Bạn đã gửi bản định hướng thành công !");
                    }
                }
            }
            return Json("Bạn đã tạo thành công !");
        }
        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public JsonResult SavePhahoi(List<DAL.Models.DHQG.KeHoach> lstkh, int statusid, int donvi, int nhomchitieu, int chot)
        {
            string jsonData = string.Empty;
            if (lstkh != null)
            {
                if (chot == 0)
                {
                    kehoachbo.DeletePhanhoi(lstkh[0].NAM_KE_HOACH_ID, statusid, donvi, nhomchitieu);
                    kehoachbo.Save(lstkh);

                    LogStream.Write<List<DAL.Models.DHQG.KeHoach>>(GetAccountLogin(), CommonData.AppName,
                    "KehoachnamController", "DeletePhanhoi", lstkh, Helper.KH_DHQGHN_CODE);

                    jsonData = "{NamKHId: " + lstkh[0].NAM_KE_HOACH_ID + ", StatusId: " + statusid + ", NhomChiTieu: " +
                        nhomchitieu + ", donvi: " + donvi + "}";
                    LogStream.Write(GetAccountLogin(), CommonData.AppName,
                        "KehoachnamController", jsonData, "Lưu phản hồi - SavePhahoi", Helper.KH_DHQGHN_CODE);
                }
                else
                {
                    NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
                    if (user != null)
                    {
                        if (user.IS_FIX == 1)
                        {
                            kehoachbo.DeletePhanhoi(lstkh[0].NAM_KE_HOACH_ID, statusid, donvi, nhomchitieu);
                            kehoachbo.Save(lstkh);

                            LogStream.Write<List<DAL.Models.DHQG.KeHoach>>(GetAccountLogin(), CommonData.AppName,
                                "KehoachnamController", "Lưu phản hồi - SavePhahoi", lstkh, Helper.KH_DHQGHN_CODE);

                            jsonData = "{NamKHId: " + lstkh[0].NAM_KE_HOACH_ID + ", StatusId: " + statusid + ", NhomChiTieu: " +
                                nhomchitieu + ", donvi: " + donvi + "}";
                            LogStream.Write(GetAccountLogin(), CommonData.AppName,
                                "KehoachnamController", jsonData, "DeletePhanhoi", Helper.KH_DHQGHN_CODE);

                            var lst = kehoachbo.CheckPhanHoi(lstkh[0].NAM_KE_HOACH_ID, statusid, donvi);
                            return Json(lst, JsonRequestBehavior.AllowGet);
                        }
                        return Json("Bạn đã tạo thành công !");
                    }
                    return Json("Bạn đã tạo thành công !");
                }
                return Json("Bạn đã tạo thành công !");
            }
            return Json("Bạn đã tạo thành công !");
        }
        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public JsonResult HuyChotPhanhoi(int namkh, int status)
        {
            var namkhh = namkehoachbo.GetById(namkh);
            if (namkhh.STATUS_ID == 50)
            {
                NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
                int b = kehoachbo.Huychot(namkh, status, user.listDonVi[0].DonViId);

                var jsonData = "{NamKHId: " + namkh + ", Status: " + status + ", donViId: " + user.listDonVi[0].DonViId + "}";
                LogStream.Write(GetAccountLogin(), CommonData.AppName,
                    "KehoachnamController", jsonData, "HuyChotPhanhoi", Helper.KH_DHQGHN_CODE);

                return Json(b, JsonRequestBehavior.AllowGet);
            }
            else
            {
                return Json("err", JsonRequestBehavior.AllowGet);
            }
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetKehoachbyNamkehoach(int namkhid, int statusid, int flag)
        {
            if (flag == 1)
            {
                mockiemsoatbo = new MocKiemSoatBO();
                namkhid = mockiemsoatbo.GetNamkehoch_Getbefore(namkhid);
            }
            List<DAL.Models.DHQG.KeHoach> lst = new List<DAL.Models.DHQG.KeHoach>();
            lst = kehoachbo.GetKehoachByNamKeHoach(namkhid, statusid, flag);
            return Json(lst, JsonRequestBehavior.AllowGet);
        }

        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetAllDvCtbyNKH(int namkhid, int flag)
        {
            List<DAL.Models.DHQG.DonViChiTieu> lst = new List<DAL.Models.DHQG.DonViChiTieu>();
            lst = donvichitieubo.GetAllDvCtbyNKH(namkhid, flag);
            return Json(lst, JsonRequestBehavior.AllowGet);
        }

        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult Getallstatus()
        {
            List<DAL.Models.DHQG.Status> lst = namkehoachbo.Getallstatus();
            return Json(lst, JsonRequestBehavior.AllowGet);
        }
        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public JsonResult UpdateStatusid(DAL.Models.DHQG.NamKeHoach obj)
        {
            int a = 0;
            List<DAL.Models.DHQG.NAMDV> lst = new List<DAL.Models.DHQG.NAMDV>();
            switch (obj.STATUS_ID)
            {
                case 20:
                    lst = namkehoachbo.CheckPhaiHoiChot(obj.NAM_KE_HOACH_ID, 21);
                    if (lst.Count > 0)
                    {
                        a = 5;
                    }
                    break;
                case 40:
                    a = 0;
                    break;
                case 50:
                    lst = namkehoachbo.CheckPhaiHoiChot(obj.NAM_KE_HOACH_ID, 51);
                    if (lst.Count > 0)
                    {
                        a = 6;
                    }
                    break;
                default:
                    a = namkehoachbo.Check(obj.NAM_KE_HOACH_ID, obj.STATUS_ID);
                    break;
            }
            if (a == 0)
            {
                obj.STATUS_DATE = DateTime.Today;
                obj.STATUS_ID = obj.STATUS_ID + 10;
                namkehoachbo.Save(obj);

                LogStream.Write<DAL.Models.DHQG.NamKeHoach>(GetAccountLogin(), CommonData.AppName,
                    "KehoachnamController", "UpdateStatusid", obj, Helper.KH_DHQGHN_CODE);
            }
            return Json(a, JsonRequestBehavior.AllowGet);
        }

        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult Getallnhomdonvi()
        {
            List<DAL.Models.DHQG.NhomDonVi> lst = nhomdvbo.GetNhomDVByType(1);
            return Json(lst, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult Getallnhomdonvinew()
        {
            List<DAL.Models.DHQG.NhomDonVi> lst = nhomdvbo.GetNhomDVByType(2);
            return Json(lst, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult Getallnhomdonviorder(int tag)
        {
            List<DAL.Models.DHQG.NhomDonVi> lst = nhomdvbo.GetNhomDVByType(tag);
            return Json(lst, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult Getallchitieubymacap2(int namchitieuid, int macap2)
        {
            List<DAL.Models.DHQG.ChitieuNew> lst = chitieubo.Getallchitieubymacap2(namchitieuid, macap2);
            return Json(lst, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult CheckNamChot(int namkh, int status, int nhomchitieu)
        {
            int a = 0;

            if (nhomchitieu == 0)
            {
                a = 1;
            }
            else
            {
                a = namkehoachbo.CheckNamChot(namkh, status, nhomchitieu);
            }

            return Json(a, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetDonviChuaPhanHoi(int namkh, int status)
        {
            List<DAL.Models.DHQG.NAMDV> lst = new List<DAL.Models.DHQG.NAMDV>();
            lst = namkehoachbo.CheckPhaiHoiChot(namkh, status);
            return Json(lst, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult CheckUpdateDieuchinh(int namkhid, int status, int nhomchitieu, int nhomdonvi)
        {
            int a = kehoachbo.CheckUpdateDieuchinh(namkhid, status, nhomchitieu, nhomdonvi);
            return Json(a, JsonRequestBehavior.AllowGet);
        }
        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public JsonResult SaveKehoachthuchien(List<DAL.Models.DHQG.KeHoachThucHien> lstkehoach, int statusid, int nhomchitieu, int nhomdonvi, int chot)
        {
            if (lstkehoach != null)
            {
                kehoachthuchienbo.DeleteKehoachthuchienbyNKH(lstkehoach[0].NAM_KE_HOACH_ID, statusid, nhomchitieu, nhomdonvi, lstkehoach[0].MOC_KIEM_SOAT_ID);
                bool a = kehoachthuchienbo.Save(lstkehoach);

                LogStream.Write<List<DAL.Models.DHQG.KeHoachThucHien>>(GetAccountLogin(), CommonData.AppName,
                    "KehoachnameController", "SaveKehoachthuchien", lstkehoach, Helper.KH_DHQGHN_CODE);

                if (chot == 1)
                {
                    var kq = kehoachthuchienbo.CheckChot(lstkehoach[0].NAM_KE_HOACH_ID, statusid, nhomdonvi, lstkehoach[0].MOC_KIEM_SOAT_ID);
                    return Json(kq, JsonRequestBehavior.AllowGet);
                }
            }
            return Json("Bạn đã tạo thành công !", JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult CheckChotbymockiemsoat(int namkh, int donviid, int mockiemsoat)
        {
            MocKiemSoatBO mc = new MocKiemSoatBO();
            int a = 0;
            var mcia = mc.GetMocKSIsActive(namkh);
            if (mcia == null)
            {
                a = 1;
            }
            else
            {
                if (mcia.MOC_KIEM_SOAT_ID != mockiemsoat)
                {
                    a = 1;
                }
                else
                {
                    a = kehoachthuchienbo.CheckChotbymockiemsoat(namkh, donviid, mockiemsoat);
                }
            }

            return Json(a, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult CheckChotbymockiemsoatNew(int namkh, int mockiemsoat)
        {
            MocKiemSoatBO mc = new MocKiemSoatBO();
            int a = 0;
            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            var mcia = mc.GetMocKSIsActive(namkh);
            a = kehoachthuchienbo.CheckChotbymockiemsoat(namkh, user.listDonVi[0].DonViId, mockiemsoat);
            if (a == 2)
            {
                if (mcia.MOC_KIEM_SOAT_ID != null)
                {
                    if (mockiemsoat == Convert.ToInt32(mcia.MOC_KIEM_SOAT_ID))
                    {
                        a = 3;
                    }
                }
            }
            return Json(a, JsonRequestBehavior.AllowGet);
        }

        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetallMockiemsoatbynkh(int namkh)
        {
            mockiemsoatbo = new MocKiemSoatBO();
            List<DAL.Models.DHQG.MocKiemSoat> lst = new List<DAL.Models.DHQG.MocKiemSoat>();
            lst = mockiemsoatbo.GetbyNamkehoach(namkh);
            return Json(lst, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetallMockiemsoatbyid(int id)
        {
            mockiemsoatbo = new MocKiemSoatBO();
            DAL.Models.DHQG.MocKiemSoat lst = new DAL.Models.DHQG.MocKiemSoat();
            lst = mockiemsoatbo.GetbyID(id);
            return Json(lst, JsonRequestBehavior.AllowGet);
        }

        public ActionResult Theodoi()
        {
            this.activeMenu("/Kehoachnam/Theodoi");

            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            if (user != null)
            {
                ViewBag.MaDV = user.listDonVi.Count > 0 ? user.listDonVi[0].MaDonVi : string.Empty;
                if (user.listDonVi.Count > 0 && user.listDonVi[0].MaDonVi == "KHTC")
                {
                    ViewBag.DonviID = "0";
                    ViewBag.URL = Url.Action("GetallChitieubynamchitieu", "Kehoachnam");
                }
                else
                {
                    ViewBag.DonviID = user.listDonVi.Count > 0 ? user.listDonVi[0].DonViId : 0;
                    ViewBag.QUYENGIAMSAT = user.listDonVi.Count > 0 ? user.listDonVi[0].CoQuyenGiamSat : false;
                    if (user.listDonVi.Count > 0)
                    {
                        if (user.listDonVi[0].CoQuyenGiamSat == true)
                        {
                            ViewBag.URL = Url.Action("GetallChitieubynamchitieu", "Kehoachnam");
                        }
                        else
                        {
                            ViewBag.URL = Url.Action("GetallChitieubynamchitieubydonvi", "Kehoachnam");

                        }

                    }
                }
            }
            return PartialView("~/Views/Kehoachnam/TheodoiPartial1.cshtml");
        }
        public ActionResult Comment()
        {
            this.activeMenu("/Kehoachnam/Comment");

            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            if (user != null)
            {
                ViewBag.DonviID = user.listDonVi.Count > 0 ? user.listDonVi[0].DonViId : 0;
                ViewBag.QUYENGIAMSAT = user.listDonVi.Count > 0 ? user.listDonVi[0].CoQuyenGiamSat : false;
                ViewBag.TenDV = user.listDonVi[0].TenDonVi;
            }
            return PartialView("~/Views/Kehoachnam/CommentPartial1.cshtml");
        }
        public ActionResult UpdateChitieu()
        {
            return PartialView("~/Views/Kehoachnam/UpdateChitieuPartial1.cshtml");
        }
        public ActionResult AddChitieu()
        {
            return PartialView("~/Views/Kehoachnam/AddChitieuPartial1.cshtml");
        }
        public ActionResult UpdateKehoachThucHien()
        {
            return PartialView("~/Views/Kehoachnam/UpdateKehoachThucHienPartial1.cshtml");
        }
        public ActionResult InsertChitieu()
        {
            return PartialView("~/Views/Kehoachnam/InsertChitieuPartial1.cshtml");
        }
        public ActionResult FeedBackComment()
        {
            this.activeMenu("/Kehoachnam/FeedBackComment");

            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            if (user != null)
            {
                ViewBag.MaDV = user.listDonVi.Count > 0 ? user.listDonVi[0].MaDonVi : string.Empty;
                if (user.listDonVi.Count > 0 && user.listDonVi[0].MaDonVi == "KHTC")
                {
                    ViewBag.DonviID = "0";
                    ViewBag.URL = Url.Action("GetallChitieubynamchitieu", "Kehoachnam");
                    ViewBag.TenDV = user.listDonVi[0].TenDonVi;
                }
                else
                {
                    ViewBag.DonviID = user.listDonVi.Count > 0 ? user.listDonVi[0].DonViId : 0;
                    ViewBag.QUYENGIAMSAT = user.listDonVi.Count > 0 ? user.listDonVi[0].CoQuyenGiamSat : false;
                    ViewBag.TenDV = user.listDonVi[0].TenDonVi;
                    if (user.listDonVi.Count > 0)
                    {
                        if (user.listDonVi[0].CoQuyenGiamSat == true)
                        {
                            ViewBag.URL = Url.Action("GetallChitieubynamchitieu", "Kehoachnam");
                        }
                        else
                        {
                            ViewBag.URL = Url.Action("GetallChitieubynamchitieubydonvi", "Kehoachnam");

                        }

                    }
                }
            }
            return PartialView("~/Views/Kehoachnam/FeedBackCommentPartial1.cshtml");
        }
        public ActionResult Baocao()
        {
            this.activeMenu("/Kehoachnam/Baocao");

            return PartialView("~/Views/Kehoachnam/BaoCaoPartial1.cshtml");
        }
        public ActionResult ShareFile()
        {
            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            ViewBag.DonviID = user.listDonVi[0].DonViId;
            ViewBag.Username = user.TAI_KHOAN;
            return PartialView("~/Views/Kehoachnam/ShareFilePartial1.cshtml");
        }

        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetallKHTH(int namkh, int flag)
        {
            List<DAL.Models.DHQG.KeHoachThucHien> lst = new List<DAL.Models.DHQG.KeHoachThucHien>();
            lst = kehoachthuchienbo.Getall(namkh, flag);
            return Json(lst, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetallKHTHNEW(int namkh, int flag, int mocks, int nhomdonvi)
        {

            List<DAL.Models.DHQG.KeHoachThucHien> lst = new List<DAL.Models.DHQG.KeHoachThucHien>();
            if (mocks == -2)
            {
                lst = kehoachthuchienbo.Getall(namkh, flag);
            }
            else
            {
                lst = kehoachthuchienbo.Getkehoachbymockiemsoat(namkh, mocks, nhomdonvi, flag);
            }

            return Json(lst, JsonRequestBehavior.AllowGet);
        }

        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetSumKH(int namkhid, int statusid)
        {
            List<DAL.Models.DHQG.SumKH> lst = new List<DAL.Models.DHQG.SumKH>();
            lst = kehoachbo.GetSumKH(namkhid, statusid);
            return Json(lst, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetSumKHTH(int namkhid, int flag)
        {
            List<DAL.Models.DHQG.SumKH> lst = new List<DAL.Models.DHQG.SumKH>();
            lst = kehoachthuchienbo.GetSumKehoachthuchien(namkhid, flag);
            return Json(lst, JsonRequestBehavior.AllowGet);
        }
        public FileResult ExpordFullData(int namKHId, int nhomdv, int baocao)
        {
            if (baocao == 0)
            {
                try
                {
                    ExcelPackage p = this.getExcelPackMucDoHoanThanhTheoMang(namKHId, nhomdv);

                    string v_filename = "MucDoHoanThanhTheoMang";
                    MemoryStream stream = new MemoryStream();
                    ZipOutputStream zipstream = new ZipOutputStream(stream);
                    zipstream.SetLevel(3); //0-9, 9 being the highest level of compression
                    // loops through the PDFs I need to create
                    var newEntry = new ZipEntry("MucDoHoanThanhTheoMang.xlsx"); //vanduong, 16/7/2015
                    newEntry.DateTime = DateTime.Now;
                    zipstream.PutNextEntry(newEntry);
                    //MemoryStream inStream = new MemoryStream(this.getExcelPackMucDoHoanThanhTheoMang(namKHId, nhomdv).GetAsByteArray());
                    MemoryStream inStream = new MemoryStream(p.GetAsByteArray());
                    StreamUtils.Copy(inStream, zipstream, new byte[4096]);
                    inStream.Close();
                    zipstream.CloseEntry();

                    zipstream.IsStreamOwner = false;
                    zipstream.Close();
                    stream.Position = 0;

                    p.Dispose();
                    return File(stream.ToArray(), "application/octet-stream", "DHQG-" + v_filename + "-.zip");
                }
                catch (Exception ex)
                {
                    throw;
                }

            }
            else
            {
                try
                {
                    ExcelPackage p = this.getExcelPackMucDoHoanThanhTheoDV(namKHId, nhomdv);
                    string v_filename = "MucDoHoanThanhTheoDV";

                    MemoryStream stream = new MemoryStream();
                    ZipOutputStream zipstream = new ZipOutputStream(stream);
                    zipstream.SetLevel(3); //0-9, 9 being the highest level of compression

                    // loops through the PDFs I need to create
                    var newEntry = new ZipEntry("MucHoanThanh.xlsx"); //vanduong, 16/7/2015
                    newEntry.DateTime = DateTime.Now;
                    zipstream.PutNextEntry(newEntry);
                    MemoryStream inStream = new MemoryStream(p.GetAsByteArray());
                    StreamUtils.Copy(inStream, zipstream, new byte[4096]);
                    inStream.Close();
                    zipstream.CloseEntry();

                    zipstream.IsStreamOwner = false;
                    zipstream.Close();
                    stream.Position = 0;

                    p.Dispose();
                    return File(stream.ToArray(), "application/octet-stream", "DHQG-" + v_filename + "-.zip");
                }
                catch (Exception ex)
                {
                    throw;
                }
            }
        }

        //phunx1, 20160507: in 3 bao cao pdf
        public FileResult ViewReportPdf(int namKHId, int nhomdv, int baocao)
        {
            if (baocao == 0)
            {
                ExcelPackage p = this.getExcelPackMucDoHoanThanhTheoMang(namKHId, nhomdv);
                MemoryStream inStream = new MemoryStream(p.GetAsByteArray());

                //phunx1, 20160422: xu ly open pdf
                XlsFile Result11 = new XlsFile(1, true);

                Result11.Open(inStream);
                Result11.PrintPaperSize = TPaperSize.A4;
                if (!isLandScape) Result11.PrintLandscape = false;
                else Result11.PrintLandscape = true;
                Result11.AutofitRowsOnWorkbook(true, true, 1);
                using (FlexCelPdfExport pdf = new FlexCelPdfExport())
                {
                    pdf.Workbook = Result11;
                    pdf.FontEmbed = FlexCel.Pdf.TFontEmbed.Embed;
                    using (MemoryStream ms = new MemoryStream())
                    {
                        pdf.BeginExport(ms);
                        pdf.ExportAllVisibleSheets(true, "BaoCaoTuan");
                        pdf.EndExport();
                        ms.Position = 0;
                        return File(ms.ToArray(), "application/pdf");
                    }
                }
                p.Dispose();
                inStream.Dispose();
            }
            else
            {
                ExcelPackage p = this.getExcelPackMucDoHoanThanhTheoDV(namKHId, nhomdv);
                MemoryStream inStream = new MemoryStream(p.GetAsByteArray());

                //phunx1, 20160422: xu ly open pdf
                XlsFile Result11 = new XlsFile(1, true);

                Result11.Open(inStream);
                Result11.PrintPaperSize = TPaperSize.A4;
                if (!isLandScape) Result11.PrintLandscape = false;
                else Result11.PrintLandscape = true;
                Result11.AutofitRowsOnWorkbook(true, true, 1);
                using (FlexCelPdfExport pdf = new FlexCelPdfExport())
                {
                    pdf.Workbook = Result11;
                    pdf.FontEmbed = FlexCel.Pdf.TFontEmbed.Embed;
                    using (MemoryStream ms = new MemoryStream())
                    {
                        pdf.BeginExport(ms);
                        pdf.ExportAllVisibleSheets(true, "BaoCaoTuan");
                        pdf.EndExport();
                        ms.Position = 0;
                        return File(ms.ToArray(), "application/pdf");
                    }
                }
                p.Dispose();
                inStream.Dispose();
            }
            return null;
        }

        private ExcelPackage getExcelPackMucDoHoanThanhTheoDV(int namKHId, int nhomdv)
        {
            var dv = nhomdvbo.GetById(nhomdv);
            string filepath = Server.MapPath("~/TemplateExcel/BM2.xlsx");
            //string v_filename = "MucDoHoanThanhTheoDV";
            System.IO.FileInfo fileInfo = new System.IO.FileInfo(filepath);

            ExcelPackage returnPackage = new ExcelPackage();
            try
            {
                ExcelPackage p = new ExcelPackage(fileInfo, true);
                ExcelWorksheet wd = p.Workbook.Worksheets[2];
                wd.Name = "Mức độ hoàn thành nhóm " + dv.TenNhomDonVi;
                int i2 = 7;
                int stt2 = 1;
                var lstdv = donvibo.GetAllDonViByNhom(nhomdv);
                wd.Cells[4, 1].Value = "Khối đơn vị " + dv.TenNhomDonVi;
                int total = 0;
                int totallonhon100 = 0;
                int totalbang100 = 0;
                int totallonhon50 = 0;
                int totalnhohon50 = 0;
                int countlonhon100 = 0;
                int countbang100 = 0;
                int countlonhon50 = 0;
                int countnhohon50 = 0;
                foreach (var item in lstdv)
                {
                    var lstkhdv = kehoachbo.GetbyDV(namKHId, 31, item.DonViId);
                    var lstkhthdv = kehoachthuchienbo.GetallByDV(namKHId, item.DonViId);
                    countlonhon100 = 0;
                    countbang100 = 0;
                    countlonhon50 = 0;
                    countnhohon50 = 0;
                    wd.Cells[i2, 1].Value = stt2;
                    wd.Cells[i2, 2].Value = item.TenDonVi;
                    wd.Cells[i2, 3].Value = lstkhdv.Count;
                    if (lstkhthdv.Count == 0)
                    {
                        countnhohon50 = lstkhdv.Count;
                    }
                    else
                    {
                        for (int g = 0; g < lstkhthdv.Count; g++)
                        {

                            if (lstkhthdv[g].GIA_TRI == null || lstkhdv[g].GIA_TRI == null)
                            {
                                countnhohon50 += 1;
                            }
                            else
                            {
                                double a = double.Parse(lstkhthdv[g].GIA_TRI);
                                double b = double.Parse(lstkhdv[g].GIA_TRI);
                                if (b == 0)
                                {
                                    countnhohon50 += 1;
                                }
                                else
                                {
                                    double c = Math.Round((a / b) * 100);
                                    if (c > 100)
                                    {
                                        countlonhon100 += 1;
                                    }
                                    if (c == 100)
                                    {
                                        countbang100 += 1;
                                    }
                                    if (c >= 50 && c < 100)
                                    {
                                        countlonhon50 += 1;
                                    }
                                    if (c < 50)
                                    {
                                        countnhohon50 += 1;
                                    }
                                }
                            }
                        }
                    }
                    wd.Cells[i2, 4].Value = countlonhon100;
                    wd.Cells[i2, 5].Value = countbang100;
                    wd.Cells[i2, 6].Value = countlonhon50;
                    wd.Cells[i2, 7].Value = countnhohon50;
                    total += lstkhdv.Count;
                    totallonhon100 += countlonhon100;
                    totalbang100 += countbang100;
                    totallonhon50 += countlonhon50;
                    totalnhohon50 += countnhohon50;
                    i2 += 1;
                    stt2 += 1;
                }
                wd.Cells[i2, 2].Value = "Tổng";
                wd.Cells[i2, 3].Value = total;
                wd.Cells[i2, 4].Value = totallonhon100;
                wd.Cells[i2, 5].Value = totalbang100;
                wd.Cells[i2, 6].Value = totallonhon50;
                wd.Cells[i2, 7].Value = totalnhohon50;
                wd.Cells[7, 1, i2, 7].Style.Border.Left.Style = ExcelBorderStyle.Thin;
                wd.Cells[7, 1, i2, 7].Style.Border.Top.Style = ExcelBorderStyle.Thin;
                wd.Cells[7, 1, i2, 7].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                wd.Cells[7, 1, i2, 7].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                wd.Cells[7, 1, i2, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                wd.Cells[7, 1, i2, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                wd.Cells[7, 3, i2, 7].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                wd.Cells[7, 3, i2, 7].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                wd.Cells[7, 1, i2, 7].Style.Font.Name = "Times New Roman";

                return p;
            }
            catch (Exception ex)
            {
                throw;
            }
            return returnPackage;
        }

        private ExcelPackage getExcelPackMucDoHoanThanhTheoMang(int namKHId, int nhomdv)
        {
            string filepath = Server.MapPath("~/TemplateExcel/BM1.xlsx");
            System.IO.FileInfo fileInfo = new System.IO.FileInfo(filepath);

            ExcelPackage returnPackage = new ExcelPackage();

            //using (ExcelPackage p = new ExcelPackage(fileInfo, true))
            //{
            try
            {
                ExcelPackage p = new ExcelPackage(fileInfo, true);
                ExcelWorksheet ws = p.Workbook.Worksheets[2];
                ws.Name = "Mức độ hoàn thành theo mảng";
                var lsttenchitieu = chitieubo.GETCAP1(namKHId);
                int i = 7;
                int stt = 1;
                var lstkh = kehoachbo.GetSumKH(namKHId, 31);
                var lskhth = kehoachthuchienbo.GetSumKehoachthuchien(namKHId, 0);
                int total = 0;
                int totallonhon100 = 0;
                int totalbang100 = 0;
                int totallonhon50 = 0;
                int totalnhohon50 = 0;
                int counttotal = 0;
                int countlonhon100 = 0;
                int countbang100 = 0;
                int countlonhon50 = 0;
                int countnhohon50 = 0;
                foreach (var item in lsttenchitieu)
                {
                    counttotal = 0;
                    countlonhon100 = 0;
                    countbang100 = 0;
                    countlonhon50 = 0;
                    countnhohon50 = 0;
                    ws.Cells[i, 1].Value = stt;
                    ws.Cells[i, 2].Value = item.TEN_CHI_TIEU;
                    for (int g = 0; g < lstkh.Count; g++)
                    {
                        if (lstkh[g].MA_CAP1 == item.MA_CAP1)
                        {
                            counttotal += 1;
                            double a = lskhth[g].GIA_TRI;
                            double b = lstkh[g].GIA_TRI;
                            if (b == 0)
                            {
                                countnhohon50 += 1;
                            }
                            else
                            {
                                double c = Math.Round((a / b) * 100);
                                if (c > 100)
                                {
                                    countlonhon100 += 1;
                                }
                                if (c == 100)
                                {
                                    countbang100 += 1;
                                }
                                if (c >= 50 && c < 100)
                                {
                                    countlonhon50 += 1;
                                }
                                if (c < 50)
                                {
                                    countnhohon50 += 1;
                                }
                            }
                        }
                    }
                    ws.Cells[i, 3].Value = counttotal;
                    ws.Cells[i, 4].Value = countlonhon100;
                    ws.Cells[i, 5].Value = countbang100;
                    ws.Cells[i, 6].Value = countlonhon50;
                    ws.Cells[i, 7].Value = countnhohon50;
                    total += counttotal;
                    totallonhon100 += countlonhon100;
                    totalbang100 += countbang100;
                    totallonhon50 += countlonhon50;
                    totalnhohon50 += countnhohon50;
                    i += 1;
                    stt += 1;
                }
                ws.Cells[i, 2].Value = "Tổng";
                ws.Cells[i, 3].Value = total;
                ws.Cells[i, 4].Value = totallonhon100;
                ws.Cells[i, 5].Value = totalbang100;
                ws.Cells[i, 6].Value = totallonhon50;
                ws.Cells[i, 7].Value = totalnhohon50;
                ws.Cells[7, 1, i, 7].Style.Border.Left.Style = ExcelBorderStyle.Thin;
                ws.Cells[7, 1, i, 7].Style.Border.Top.Style = ExcelBorderStyle.Thin;
                ws.Cells[7, 1, i, 7].Style.Border.Right.Style = ExcelBorderStyle.Thin;
                ws.Cells[7, 1, i, 7].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                ws.Cells[7, 1, i, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                ws.Cells[7, 1, i, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                ws.Cells[7, 3, i, 7].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                ws.Cells[7, 3, i, 7].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                ws.Cells[7, 1, i, 7].Style.Font.Name = "Times New Roman";

                return p;
            }
            catch (Exception ex)
            {
                throw;
            }
            //}
            return returnPackage;

        }

        public FileResult ExportData(int namKHId, int nhomDonViId, int donViId, string namKH)
        {
            string tenNhomDV = string.Empty;
            if (nhomDonViId > 0)
            {
                NhomDonViBO ndvObj = new NhomDonViBO();
                NhomDonVi obj = ndvObj.GetById(nhomDonViId);
                if (obj != null) tenNhomDV = "NHÓM " + obj.TenNhomDonVi.ToUpper();
            }

            ExportExcellBO sqlBO = new ExportExcellBO();
            int laChiTieuChinhThuc = 31;
            int laChiTieuDieuChinh = 61;
            List<DonVi> listDonVi = new List<DonVi>();
            if (donViId > 0)
            {
                listDonVi.Add(donvibo.GetById(donViId));
            }
            else listDonVi = donvibo.GetAllDonViByNhom(nhomDonViId);

            List<ExportExcell> chiTieuChinhThucDHQG = sqlBO.GetListChiTieuDHQG_TheoNhom(namKHId, laChiTieuChinhThuc, donViId, nhomDonViId);
            var keHoachThucHienDHQG = sqlBO.GetListKeHoachThucHienDHQG_TheoNhom(namKHId, laChiTieuChinhThuc, donViId, nhomDonViId);
            var keHoachDieuChinhDHQG = sqlBO.GetListChiTieuDHQG_TheoNhom(namKHId, laChiTieuDieuChinh, donViId, nhomDonViId);

            List<List<ExportExcell>> dsChiTieuDonVi = new List<List<ExportExcell>>();
            List<List<ExportExcell>> dsKHThucHienDonVi = new List<List<ExportExcell>>();
            List<ExportExcell> dataDonVi;
            for (int i = 0; i < listDonVi.Count; i++)
            {
                dataDonVi = sqlBO.GetListChiTieuDonVi_TheoNhom(namKHId, laChiTieuDieuChinh, listDonVi[i].DonViId, nhomDonViId);
                if (!HasData(dataDonVi))
                {
                    dataDonVi = sqlBO.GetListChiTieuDonVi_TheoNhom(namKHId, laChiTieuChinhThuc, listDonVi[i].DonViId, nhomDonViId);
                }
                dsChiTieuDonVi.Add(dataDonVi);
                dsKHThucHienDonVi.Add(sqlBO.GetListKeHoachThucHienDonVi_TheoNhom(namKHId, laChiTieuChinhThuc, listDonVi[i].DonViId, nhomDonViId));
            }

            Color backGroundColor = Color.FromArgb(208, 206, 206);
            using (var excelPackage = new ExcelPackage())
            {
                excelPackage.Workbook.Properties.Author = "vanduong";
                excelPackage.Workbook.Properties.Title = "Export excel";
                var sheet = excelPackage.Workbook.Worksheets.Add("Tình hình thực hiện kế hoạch " + namKH);

                #region Set Margion
                sheet.PrinterSettings.TopMargin = topMargin;
                sheet.PrinterSettings.BottomMargin = bottomMargin;
                sheet.PrinterSettings.LeftMargin = leftMargin;
                sheet.PrinterSettings.RightMargin = rightMargin;
                sheet.PrinterSettings.FitToWidth = 1;
                #endregion

                #region Set column width and set text alignment
                sheet.Column(2).Width = dvtColumnWidth;
                for (int i = 3; i < listDonVi.Count * 3 + 8; i++)
                {
                    sheet.Column(i).Width = dvColumnWidth;
                    sheet.Column(i).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                }
                #endregion

                var printArea = sheet.Cells[1, 1, 1000, listDonVi.Count * 3 + 7];
                printArea.Style.Font.SetFromFont(new Font("Arial", 10));

                // output a line for the headers
                sheet.Name = "Tình hình thực hiện KH " + namKHId;
                int startRowLogo = 1;
                int startColumnLogo = 1;
                var donViCell = sheet.Cells[startRowLogo, startColumnLogo, startRowLogo, startColumnLogo];
                donViCell.Merge = true;
                donViCell.Value = "ĐẠI HỌC QUỐC GIA HÀ NỘI";
                donViCell.Style.Font.Bold = true;
                donViCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                startRowLogo += 1;
                if (donViId > 0)
                {
                    donViCell = sheet.Cells[startRowLogo, startColumnLogo, startRowLogo, startColumnLogo];
                    donViCell.Merge = true;
                    donViCell.Value = listDonVi.Count > 0 ? listDonVi[0].TenDonVi.ToUpper() : string.Empty;
                    donViCell.Style.Font.Bold = true;
                    donViCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                }
                else
                {
                    donViCell = sheet.Cells[startRowLogo, startColumnLogo, startRowLogo, startColumnLogo];
                    donViCell.Merge = true;
                    donViCell.Value = tenNhomDV;
                    donViCell.Style.Font.Bold = true;
                    donViCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                }

                int startRowTile = startRowLogo + 1;
                var titleCell = sheet.Cells[startRowTile, 2, startRowTile, listDonVi.Count * 3 + 7];
                titleCell.Merge = true;
                titleCell.Value = "BÁO CÁO TIẾN ĐỘ THỰC HIỆN KẾ HOẠCH NĂM " + namKH;
                titleCell.Style.Font.SetFromFont(new Font("Arial", 12));
                titleCell.Style.Font.Bold = true;
                titleCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                titleCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                #region Thong tin moc kiem soat
                int namkh = 0;
                if (!string.IsNullOrEmpty(namKH))
                {
                    try
                    {
                        namkh = System.Convert.ToInt32(namKH);
                    }
                    catch { }
                }
                MocKiemSoatBO mc = new MocKiemSoatBO();
                MocKiemSoat mks = mc.GetMocKSIsActive(namkh);

                string kyBC = string.Empty;
                if (mks != null)
                {
                    kyBC = mks.MOC_KIEM_SOAT;
                }
                startRowTile += 1;
                var kyBaoCaoCell = sheet.Cells[startRowTile, 2, startRowTile, listDonVi.Count * 3 + 7];
                kyBaoCaoCell.Merge = true;
                kyBaoCaoCell.Value = "Kỳ báo cáo: " + kyBC;
                kyBaoCaoCell.Style.Font.Bold = true;
                kyBaoCaoCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                kyBaoCaoCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                #endregion

                startRow = startRowTile + 1;
                var cellRange1 = sheet.Cells[startRow, startCol, startRow + 1, startCol];
                cellRange1.Merge = true;
                cellRange1.Value = "Chỉ tiêu";
                cellRange1.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cellRange1.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                cellRange1.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange1.Style.Font.Bold = true;
                //ExcelColumn colChiTieu = sheet.Column(startCol);
                //colChiTieu.Style.WrapText = true;
                //colChiTieu.Width = 60;

                #region Set width of column ChiTieu
                sheet.Column(startCol).Style.WrapText = true;
                double chitieuWidthMin = 30;
                double cacDonViWidth = (listDonVi.Count * 3 + 5) * dvColumnWidth + dvtColumnWidth;
                double chitieuWidth = portraitSize - cacDonViWidth;
                if (chitieuWidth < chitieuWidthMin)
                {
                    chitieuWidth = Math.Max(chitieuWidthMin, landscapeSize - cacDonViWidth);
                    isLandScape = true;
                }
                sheet.Column(startCol).Width = chitieuWidth;
                #endregion

                cellRange1 = sheet.Cells[startRow, startCol + 1, startRow + 1, startCol + 1];
                cellRange1.Merge = true;
                cellRange1.Value = "Đơn vị tính";
                cellRange1.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cellRange1.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                cellRange1.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange1.Style.Font.Bold = true;
                ExcelColumn colDonVi = sheet.Column(startCol + 1);
                colDonVi.Style.WrapText = true;
                colDonVi.Width = 15;

                var cellRange2 = sheet.Cells[startRow, startCol + 2, startRow, startCol + 6];
                cellRange2.Merge = true;
                cellRange2.Value = "ĐHQGHN";

                cellRange2 = sheet.Cells[startRow + 1, startCol + 2];
                cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange2.Value = "KH";
                cellRange2 = sheet.Cells[startRow + 1, startCol + 3];
                cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange2.Value = "%";
                cellRange2 = sheet.Cells[startRow + 1, startCol + 4];
                cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange2.Value = "ĐC";
                cellRange2 = sheet.Cells[startRow + 1, startCol + 5];
                cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange2.Value = "%";
                cellRange2 = sheet.Cells[startRow + 1, startCol + 6];
                cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange2.Value = "TTH";

                int colStart = startCol + 7;
                for (int i = 0; i < listDonVi.Count; i++)
                {
                    cellRange2 = sheet.Cells[startRow, colStart, startRow, colStart + 2];
                    cellRange2.Merge = true;
                    cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                    cellRange2.Value = listDonVi[i].TenVietTat;

                    cellRange2 = sheet.Cells[startRow + 1, colStart];
                    cellRange2.Value = "KH";
                    cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);

                    cellRange2 = sheet.Cells[startRow + 1, colStart + 1];
                    cellRange2.Value = "TH";
                    cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);

                    cellRange2 = sheet.Cells[startRow + 1, colStart + 2];
                    cellRange2.Value = "%";
                    cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                    colStart = colStart + 3;
                }
                cellRange2 = sheet.Cells[startRow, startCol + 2, startRow + 1, colStart - 1];
                cellRange2.Style.Font.Bold = true;
                cellRange2.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cellRange2.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);

                int beginRow = startRow + 1;
                int beginCol = startCol;
                for (int i = 0; i < chiTieuChinhThucDHQG.Count; i++)
                {
                    beginRow++;
                    cellRange2 = sheet.Cells[beginRow, startCol];
                    cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                    if (chiTieuChinhThucDHQG[i].CAP == 1)
                    {
                        cellRange2.Value = chiTieuChinhThucDHQG[i].TEN_CHI_TIEU.ToUpper();
                    }
                    else cellRange2.Value = chiTieuChinhThucDHQG[i].TEN_CHI_TIEU;

                    if (chiTieuChinhThucDHQG[i].CAP == 1 ||
                        chiTieuChinhThucDHQG[i].CAP == 2)
                    {
                        cellRange2.Style.Font.Bold = true;
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);

                        //Don vi tinh
                        cellRange2 = sheet.Cells[beginRow, startCol + 1];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);

                        //KH
                        cellRange2 = sheet.Cells[beginRow, startCol + 2];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);

                        //%
                        cellRange2 = sheet.Cells[beginRow, startCol + 3];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);
                        //DC
                        cellRange2 = sheet.Cells[beginRow, startCol + 4];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);
                        //%
                        cellRange2 = sheet.Cells[beginRow, startCol + 5];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);
                        //Tổng TH
                        cellRange2 = sheet.Cells[beginRow, startCol + 6];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);
                    }

                    if (chiTieuChinhThucDHQG[i].CAP > 2)
                    {
                        cellRange2 = sheet.Cells[beginRow, startCol + 1];
                        cellRange2.Value = chiTieuChinhThucDHQG[i].DON_VI_TINH;
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        //KH
                        cellRange2 = sheet.Cells[beginRow, startCol + 2];
                        cellRange2.Value = chiTieuChinhThucDHQG[i].GIA_TRI;
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        //%
                        cellRange2 = sheet.Cells[beginRow, startCol + 3];
                        cellRange2.Value = chiTieuChinhThucDHQG[i].GIA_TRI > 0 ?
                            Math.Round(((float)keHoachThucHienDHQG[i].GIA_TRI / chiTieuChinhThucDHQG[i].GIA_TRI) * 100) : 0;
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        //DC
                        cellRange2 = sheet.Cells[beginRow, startCol + 4];
                        cellRange2.Value = keHoachDieuChinhDHQG[i].GIA_TRI;
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        //%
                        cellRange2 = sheet.Cells[beginRow, startCol + 5];
                        cellRange2.Value = chiTieuChinhThucDHQG[i].GIA_TRI > 0 ?
                            Math.Round(((float)keHoachDieuChinhDHQG[i].GIA_TRI / chiTieuChinhThucDHQG[i].GIA_TRI) * 100) : 0;
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        //Tổng TH
                        cellRange2 = sheet.Cells[beginRow, startCol + 6];
                        cellRange2.Value = chiTieuChinhThucDHQG[i].GIA_TRI > 0 ? keHoachThucHienDHQG[i].GIA_TRI : 0;
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                    }
                }
                cellRange2 = sheet.Cells[startRow + 1, startCol, beginRow, startCol + 1];
                cellRange2.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cellRange2.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;

                colStart = startCol + 7;
                beginRow = startRow + 1;
                for (int i = 0; i < dsChiTieuDonVi.Count; i++)
                {
                    int len = dsChiTieuDonVi[i].Count;
                    beginRow = startRow + 1;
                    for (int j = 0; j < len; j++)
                    {
                        beginRow++;
                        cellRange2 = sheet.Cells[beginRow, colStart];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        if (dsChiTieuDonVi[i][j].CAP > 2)
                        {
                            cellRange2 = sheet.Cells[beginRow, colStart];
                            cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);

                            cellRange2.Value = dsChiTieuDonVi[i][j].GIA_TRI;
                            cellRange2 = sheet.Cells[beginRow, colStart + 1];
                            cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                            cellRange2.Value = dsKHThucHienDonVi[i][j].GIA_TRI;

                            cellRange2 = sheet.Cells[beginRow, colStart + 2];
                            cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                            cellRange2.Value = dsChiTieuDonVi[i][j].GIA_TRI > 0 ?
                                Math.Round(((float)dsKHThucHienDonVi[i][j].GIA_TRI / dsChiTieuDonVi[i][j].GIA_TRI) * 100) : 0;
                        }
                        else
                        {
                            cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                            cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);

                            cellRange2 = sheet.Cells[beginRow, colStart + 1];
                            cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                            cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);
                            cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);

                            cellRange2 = sheet.Cells[beginRow, colStart + 2];
                            cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                            cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);
                            cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        }
                    }
                    colStart = colStart + 3;
                }

                FileResult result = File(excelPackage.GetAsByteArray(), "application/octet-stream", "TinhHinhThucHienKeHoach" + namKH + ".xlsx");
                //stream.Dispose();

                return result;
            }
        }

        public FileResult ExportDataDHQG(int namKHId, string namKH)
        {
            string tenNhomDV = string.Empty;

            ExportExcellBO sqlBO = new ExportExcellBO();
            int laChiTieuChinhThuc = 31;
            int laChiTieuDieuChinh = 61;
            List<ExportExcell> chiTieuChinhThucDHQG = sqlBO.GetListChiTieuDHQG(namKHId, laChiTieuChinhThuc);
            var keHoachThucHienDHQG = sqlBO.GetListKeHoachThucHienDHQG(namKHId, laChiTieuChinhThuc);
            var keHoachDieuChinhDHQG = sqlBO.GetListChiTieuDHQG(namKHId, laChiTieuDieuChinh);
            List<DonVi> listDonVi = donvibo.GetAll();
            List<List<ExportExcell>> dsKHThucHienDonVi = new List<List<ExportExcell>>();
            for (int i = 0; i < listDonVi.Count; i++)
            {
                dsKHThucHienDonVi.Add(sqlBO.GetListKeHoachThucHienDonVi(namKHId, laChiTieuChinhThuc, listDonVi[i].DonViId));
            }
            List<double> tongThucHien = TongThucHien(namKHId);

            Color backGroundColor = Color.FromArgb(208, 206, 206);
            using (var excelPackage = new ExcelPackage())
            {
                excelPackage.Workbook.Properties.Author = "vanduong";
                excelPackage.Workbook.Properties.Title = "Export excel";
                var sheet = excelPackage.Workbook.Worksheets.Add("Tình hình thực hiện kế hoạch " + namKH);
                sheet.PrinterSettings.TopMargin = topMargin;
                sheet.PrinterSettings.BottomMargin = bottomMargin;
                sheet.PrinterSettings.LeftMargin = leftMargin;
                sheet.PrinterSettings.RightMargin = rightMargin;

                var printArea = sheet.Cells[1, 1, 1000, listDonVi.Count * 3 + 7];
                printArea.Style.Font.SetFromFont(new Font("Arial", 10));

                // output a line for the headers
                sheet.Name = "Tình hình thực hiện KH " + namKHId;
                int startRowLogo = 1;
                var donViCell = sheet.Cells[startRowLogo, 1, startRowLogo, 1];
                donViCell.Merge = true;
                donViCell.Value = "ĐẠI HỌC QUỐC GIA HÀ NỘI";
                donViCell.Style.Font.Bold = true;
                donViCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;

                int startRowTile = 3;
                var titleCell = sheet.Cells[startRowTile, 1, startRowTile, 7];
                titleCell.Merge = true;
                titleCell.Value = "BÁO CÁO TIẾN ĐỘ THỰC HIỆN KẾ HOẠCH NĂM " + namKH;
                titleCell.Style.Font.SetFromFont(new Font("Arial", 12));
                titleCell.Style.Font.Bold = true;
                titleCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                titleCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                #region Thong tin moc kiem soat
                int namkh = 0;
                if (!string.IsNullOrEmpty(namKH))
                {
                    try
                    {
                        namkh = System.Convert.ToInt32(namKH);
                    }
                    catch { }
                }
                MocKiemSoatBO mc = new MocKiemSoatBO();
                MocKiemSoat mks = mc.GetMocKSIsActive(namkh);

                string kyBC = string.Empty;
                if (mks != null)
                {
                    kyBC = mks.MOC_KIEM_SOAT;
                }
                startRowTile += 1;
                var kyBaoCaoCell = sheet.Cells[startRowTile, 1, startRowTile, 7];
                kyBaoCaoCell.Merge = true;
                kyBaoCaoCell.Value = "Kỳ báo cáo: " + kyBC;
                kyBaoCaoCell.Style.Font.Bold = true;
                kyBaoCaoCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                kyBaoCaoCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                #endregion

                startRow = startRowTile + 1;
                var cellRange1 = sheet.Cells[startRow, startCol, startRow + 1, startCol];
                cellRange1.Merge = true;
                cellRange1.Value = "Chỉ tiêu";
                cellRange1.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cellRange1.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                cellRange1.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange1.Style.Font.Bold = true;
                ExcelColumn colChiTieu = sheet.Column(startCol);
                colChiTieu.Style.WrapText = true;
                colChiTieu.Width = 60;

                cellRange1 = sheet.Cells[startRow, startCol + 1, startRow + 1, startCol + 1];
                cellRange1.Merge = true;
                cellRange1.Value = "Đơn vị tính";
                cellRange1.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cellRange1.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                cellRange1.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange1.Style.Font.Bold = true;
                ExcelColumn colDonVi = sheet.Column(startCol + 1);
                colDonVi.Style.WrapText = true;
                colDonVi.Width = 15;

                var cellRange2 = sheet.Cells[startRow, startCol + 2, startRow, startCol + 6];
                cellRange2.Merge = true;
                cellRange2.Value = "ĐHQGHN";
                cellRange2.Style.Font.Bold = true;
                cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange2.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cellRange2.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                cellRange2 = sheet.Cells[startRow + 1, startCol + 2];
                cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange2.Value = "KH";
                cellRange2.Style.Font.Bold = true;
                cellRange2.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cellRange2.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                cellRange2 = sheet.Cells[startRow + 1, startCol + 3];
                cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange2.Value = "%";
                cellRange2.Style.Font.Bold = true;
                cellRange2.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cellRange2.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                cellRange2 = sheet.Cells[startRow + 1, startCol + 4];
                cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange2.Value = "ĐC";
                cellRange2.Style.Font.Bold = true;
                cellRange2.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cellRange2.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                cellRange2 = sheet.Cells[startRow + 1, startCol + 5];
                cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange2.Value = "%";
                cellRange2.Style.Font.Bold = true;
                cellRange2.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cellRange2.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                cellRange2 = sheet.Cells[startRow + 1, startCol + 6];
                cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange2.Value = "TTH";
                cellRange2.Style.Font.Bold = true;
                cellRange2.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cellRange2.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                int beginRow = startRow + 1;
                int beginCol = startCol;
                for (int i = 0; i < chiTieuChinhThucDHQG.Count; i++)
                {
                    beginRow++;
                    cellRange2 = sheet.Cells[beginRow, startCol];
                    cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                    if (chiTieuChinhThucDHQG[i].CAP == 1)
                    {
                        cellRange2.Value = chiTieuChinhThucDHQG[i].TEN_CHI_TIEU.ToUpper();
                    }
                    else cellRange2.Value = chiTieuChinhThucDHQG[i].TEN_CHI_TIEU;

                    if (chiTieuChinhThucDHQG[i].CAP == 1 ||
                        chiTieuChinhThucDHQG[i].CAP == 2)
                    {
                        cellRange2.Style.Font.Bold = true;
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);

                        //Don vi tinh
                        cellRange2 = sheet.Cells[beginRow, startCol + 1];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);

                        //KH
                        cellRange2 = sheet.Cells[beginRow, startCol + 2];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);

                        //%
                        cellRange2 = sheet.Cells[beginRow, startCol + 3];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);
                        //DC
                        cellRange2 = sheet.Cells[beginRow, startCol + 4];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);
                        //%
                        cellRange2 = sheet.Cells[beginRow, startCol + 5];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);
                        //Tổng TH
                        cellRange2 = sheet.Cells[beginRow, startCol + 6];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);
                    }

                    if (chiTieuChinhThucDHQG[i].CAP > 2)
                    {
                        cellRange2 = sheet.Cells[beginRow, startCol + 1];
                        cellRange2.Value = chiTieuChinhThucDHQG[i].DON_VI_TINH;
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        //KH
                        cellRange2 = sheet.Cells[beginRow, startCol + 2];
                        cellRange2.Value = chiTieuChinhThucDHQG[i].GIA_TRI;
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        //%
                        cellRange2 = sheet.Cells[beginRow, startCol + 3];
                        cellRange2.Value = chiTieuChinhThucDHQG[i].GIA_TRI > 0 ?
                            Math.Round(((float)keHoachThucHienDHQG[i].GIA_TRI / chiTieuChinhThucDHQG[i].GIA_TRI) * 100) : 0;
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        //DC
                        cellRange2 = sheet.Cells[beginRow, startCol + 4];
                        cellRange2.Value = keHoachDieuChinhDHQG[i].GIA_TRI;
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        //%
                        cellRange2 = sheet.Cells[beginRow, startCol + 5];
                        cellRange2.Value = chiTieuChinhThucDHQG[i].GIA_TRI > 0 ?
                            Math.Round(((float)keHoachDieuChinhDHQG[i].GIA_TRI / chiTieuChinhThucDHQG[i].GIA_TRI) * 100) : 0;
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        //Tổng TH
                        cellRange2 = sheet.Cells[beginRow, startCol + 6];
                        cellRange2.Value = tongThucHien[i];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                    }
                }
                cellRange2 = sheet.Cells[startRow + 1, startCol, beginRow, startCol + 1];
                cellRange2.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cellRange2.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;

                //FileResult result = File(stream.ToArray(), "application/octet-stream", "TinhHinhThucHienKeHoach" + namKHId + ".zip");
                FileResult result = File(excelPackage.GetAsByteArray(),
                    "application/octet-stream", "TinhHinhThucHienKeHoachDHQG" + namKH + ".xlsx");
                excelPackage.Dispose();

                return result;

            }


        }

        public FileResult ExportDataDonVi(int namKHId, int donViId, string namKH)
        {
            int nhomDonViId = 0;
            ExportExcellBO sqlBO = new ExportExcellBO();
            int laChiTieuChinhThuc = 31;
            int laChiTieuDieuChinh = 61;
            List<DonVi> listDonVi = new List<DonVi>();
            listDonVi.Add(donvibo.GetById(donViId));

            List<List<ExportExcell>> dsChiTieuDonVi = new List<List<ExportExcell>>();
            List<List<ExportExcell>> dsKHThucHienDonVi = new List<List<ExportExcell>>();
            List<ExportExcell> dataDonVi;
            for (int i = 0; i < listDonVi.Count; i++)
            {
                dataDonVi = sqlBO.GetListChiTieuDonVi_TheoNhom(namKHId, laChiTieuDieuChinh, listDonVi[i].DonViId, nhomDonViId);
                if (!HasData(dataDonVi))
                {
                    dataDonVi = sqlBO.GetListChiTieuDonVi_TheoNhom(namKHId, laChiTieuChinhThuc, listDonVi[i].DonViId, nhomDonViId);
                }
                dsChiTieuDonVi.Add(dataDonVi);
                dsKHThucHienDonVi.Add(sqlBO.GetListKeHoachThucHienDonVi_TheoNhom(namKHId, laChiTieuChinhThuc, listDonVi[i].DonViId, nhomDonViId));
            }

            Color backGroundColor = Color.FromArgb(208, 206, 206);
            using (var excelPackage = new ExcelPackage())
            {
                excelPackage.Workbook.Properties.Author = "vanduong";
                excelPackage.Workbook.Properties.Title = "Export excel";
                var sheet = excelPackage.Workbook.Worksheets.Add("Tình hình thực hiện kế hoạch " + namKH);
                sheet.PrinterSettings.TopMargin = topMargin;
                sheet.PrinterSettings.BottomMargin = bottomMargin;
                sheet.PrinterSettings.LeftMargin = leftMargin;
                sheet.PrinterSettings.RightMargin = rightMargin;

                var printArea = sheet.Cells[1, 1, 1000, listDonVi.Count * 3 + 7];
                printArea.Style.Font.SetFromFont(new Font("Arial", 10));

                // output a line for the headers
                sheet.Name = "Tình hình thực hiện KH " + namKHId;
                int startRowLogo = 1;
                int startColumnLogo = 1;
                var donViCell = sheet.Cells[startRowLogo, startColumnLogo, startRowLogo, startColumnLogo];
                donViCell.Merge = true;
                donViCell.Value = "ĐẠI HỌC QUỐC GIA HÀ NỘI";
                donViCell.Style.Font.Bold = true;
                donViCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                startRowLogo += 1;
                donViCell = sheet.Cells[startRowLogo, startColumnLogo, startRowLogo, startColumnLogo];
                donViCell.Merge = true;
                donViCell.Value = listDonVi.Count > 0 ? listDonVi[0].TenDonVi.ToUpper() : string.Empty;
                donViCell.Style.Font.Bold = true;
                donViCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;

                int startRowTile = startRowLogo + 1;
                var titleCell = sheet.Cells[startRowTile, 1, startRowTile, listDonVi.Count * 3 + 2];
                titleCell.Merge = true;
                titleCell.Value = "BÁO CÁO TIẾN ĐỘ THỰC HIỆN KẾ HOẠCH NĂM " + namKH;
                titleCell.Style.Font.SetFromFont(new Font("Arial", 12));
                titleCell.Style.Font.Bold = true;
                titleCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                titleCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                #region Thong tin moc kiem soat
                int namkh = 0;
                if (!string.IsNullOrEmpty(namKH))
                {
                    try
                    {
                        namkh = System.Convert.ToInt32(namKH);
                    }
                    catch { }
                }
                MocKiemSoatBO mc = new MocKiemSoatBO();
                MocKiemSoat mks = mc.GetMocKSIsActive(namkh);

                string kyBC = string.Empty;
                if (mks != null)
                {
                    kyBC = mks.MOC_KIEM_SOAT;
                }
                startRowTile += 1;
                var kyBaoCaoCell = sheet.Cells[startRowTile, 1, startRowTile, listDonVi.Count * 3 + 2];
                kyBaoCaoCell.Merge = true;
                kyBaoCaoCell.Value = "Kỳ báo cáo: " + kyBC;
                kyBaoCaoCell.Style.Font.Bold = true;
                kyBaoCaoCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                kyBaoCaoCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                #endregion

                startRow = startRowTile + 1;
                var cellRange1 = sheet.Cells[startRow, startCol, startRow + 1, startCol];
                cellRange1.Merge = true;
                cellRange1.Value = "Chỉ tiêu";
                cellRange1.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cellRange1.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                cellRange1.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange1.Style.Font.Bold = true;
                ExcelColumn colChiTieu = sheet.Column(startCol);
                colChiTieu.Style.WrapText = true;
                colChiTieu.Width = 60;

                cellRange1 = sheet.Cells[startRow, startCol + 1, startRow + 1, startCol + 1];
                cellRange1.Merge = true;
                cellRange1.Value = "Đơn vị tính";
                cellRange1.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cellRange1.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                cellRange1.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange1.Style.Font.Bold = true;
                ExcelColumn colDonVi = sheet.Column(startCol + 1);
                colDonVi.Style.WrapText = true;
                colDonVi.Width = 15;
                ExcelRange cellRange2 = null;
                int colStart = startCol + 2;
                for (int i = 0; i < listDonVi.Count; i++)
                {
                    cellRange2 = sheet.Cells[startRow, colStart, startRow, colStart + 2];
                    cellRange2.Merge = true;
                    cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                    cellRange2.Value = listDonVi[i].TenVietTat;

                    cellRange2 = sheet.Cells[startRow + 1, colStart];
                    cellRange2.Value = "KH";
                    cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);

                    cellRange2 = sheet.Cells[startRow + 1, colStart + 1];
                    cellRange2.Value = "TH";
                    cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);

                    cellRange2 = sheet.Cells[startRow + 1, colStart + 2];
                    cellRange2.Value = "%";
                    cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                    colStart = colStart + 3;
                }
                cellRange2 = sheet.Cells[startRow, startCol + 2, startRow + 1, colStart - 1];
                cellRange2.Style.Font.Bold = true;
                cellRange2.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cellRange2.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);

                int beginRow = startRow + 1;
                int beginCol = startCol;

                if (dsChiTieuDonVi == null || dsChiTieuDonVi.Count == 0)
                    return null;
                #region Temp
                List<ExportExcell> danhSachChiTieu = dsChiTieuDonVi[0];
                for (int i = 0; i < danhSachChiTieu.Count; i++)
                {
                    beginRow++;
                    cellRange2 = sheet.Cells[beginRow, startCol];
                    cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                    if (danhSachChiTieu[i].CAP == 1)
                    {
                        cellRange2.Value = danhSachChiTieu[i].TEN_CHI_TIEU.ToUpper();
                    }
                    else cellRange2.Value = danhSachChiTieu[i].TEN_CHI_TIEU;

                    if (danhSachChiTieu[i].CAP == 1 ||
                        danhSachChiTieu[i].CAP == 2)
                    {
                        cellRange2.Style.Font.Bold = true;
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);

                        //Don vi tinh
                        cellRange2 = sheet.Cells[beginRow, startCol + 1];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);
                    }
                    else
                    {
                        cellRange2 = sheet.Cells[beginRow, startCol + 1];
                        cellRange2.Value = danhSachChiTieu[i].DON_VI_TINH;
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                    }
                }
                cellRange2 = sheet.Cells[startRow + 1, startCol, beginRow, startCol + 1];
                cellRange2.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cellRange2.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
                #endregion

                colStart = startCol + 2;
                beginRow = startRow + 1;
                for (int i = 0; i < dsChiTieuDonVi.Count; i++)
                {
                    int len = dsChiTieuDonVi[i].Count;
                    beginRow = startRow + 1;
                    for (int j = 0; j < len; j++)
                    {
                        beginRow++;
                        cellRange2 = sheet.Cells[beginRow, colStart];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        if (dsChiTieuDonVi[i][j].CAP > 2)
                        {
                            cellRange2 = sheet.Cells[beginRow, colStart];
                            cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);

                            cellRange2.Value = dsChiTieuDonVi[i][j].GIA_TRI;
                            cellRange2 = sheet.Cells[beginRow, colStart + 1];
                            cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                            cellRange2.Value = dsKHThucHienDonVi[i][j].GIA_TRI;

                            cellRange2 = sheet.Cells[beginRow, colStart + 2];
                            cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                            cellRange2.Value = dsChiTieuDonVi[i][j].GIA_TRI > 0 ?
                                Math.Round(((float)dsKHThucHienDonVi[i][j].GIA_TRI / dsChiTieuDonVi[i][j].GIA_TRI) * 100) : 0;
                        }
                        else
                        {
                            cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                            cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);

                            cellRange2 = sheet.Cells[beginRow, colStart + 1];
                            cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                            cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);
                            cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);

                            cellRange2 = sheet.Cells[beginRow, colStart + 2];
                            cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                            cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);
                            cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        }
                    }
                    colStart = colStart + 3;
                }

                FileResult result = File(excelPackage.GetAsByteArray(), "application/octet-stream", "TinhHinhThucHienKeHoach" + namKH + ".xlsx");
                excelPackage.Dispose();

                return result;
            }
        }

        [HttpPost]
        public FileResult ExportData1(int namKHId, int nhomDonViId, int donViId, string namKH)
        {
            string tenNhomDV = string.Empty;
            if (nhomDonViId > 0)
            {
                NhomDonViBO ndvObj = new NhomDonViBO();
                NhomDonVi obj = ndvObj.GetById(nhomDonViId);
                if (obj != null) tenNhomDV = "NHÓM " + obj.TenNhomDonVi.ToUpper();
            }

            ExportExcellBO sqlBO = new ExportExcellBO();
            int laChiTieuChinhThuc = 31;
            int laChiTieuDieuChinh = 61;
            List<ExportExcell> chiTieuChinhThucDHQG = sqlBO.GetListChiTieuDHQG(namKHId, laChiTieuChinhThuc);
            var keHoachThucHienDHQG = sqlBO.GetListKeHoachThucHienDHQG(namKHId, laChiTieuChinhThuc);
            var keHoachDieuChinhDHQG = sqlBO.GetListChiTieuDHQG(namKHId, laChiTieuDieuChinh);
            List<DonVi> listDonVi = new List<DonVi>();
            if (donViId > 0)
            {
                listDonVi.Add(donvibo.GetById(donViId));
            }
            else listDonVi = donvibo.GetAllDonViByNhom(nhomDonViId);

            List<List<ExportExcell>> dsChiTieuDonVi = new List<List<ExportExcell>>();
            List<List<ExportExcell>> dsKHThucHienDonVi = new List<List<ExportExcell>>();

            for (int i = 0; i < listDonVi.Count; i++)
            {
                dsChiTieuDonVi.Add(sqlBO.GetListChiTieuDonVi(namKHId, laChiTieuChinhThuc, listDonVi[i].DonViId));
                dsKHThucHienDonVi.Add(sqlBO.GetListKeHoachThucHienDonVi(namKHId, laChiTieuChinhThuc, listDonVi[i].DonViId));
            }

            Color backGroundColor = Color.FromArgb(208, 206, 206);
            using (var excelPackage = new ExcelPackage())
            {
                excelPackage.Workbook.Properties.Author = "vanduong";
                excelPackage.Workbook.Properties.Title = "Export excel";
                var sheet = excelPackage.Workbook.Worksheets.Add("Tình hình thực hiện kế hoạch " + namKH);
                sheet.PrinterSettings.TopMargin = topMargin;
                sheet.PrinterSettings.BottomMargin = bottomMargin;
                sheet.PrinterSettings.LeftMargin = leftMargin;
                sheet.PrinterSettings.RightMargin = rightMargin;

                var printArea = sheet.Cells[1, 1, 1000, listDonVi.Count * 3 + 7];
                printArea.Style.Font.SetFromFont(new Font("Arial", 10));

                // output a line for the headers
                sheet.Name = "Tình hình thực hiện KH " + namKHId;
                int startRowLogo = 1;
                var donViCell = sheet.Cells[startRowLogo, 1, startRowLogo, 1];
                donViCell.Merge = true;
                donViCell.Value = "ĐẠI HỌC QUỐC GIA HÀ NỘI";
                donViCell.Style.Font.Bold = true;
                donViCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                if (donViId > 0)
                {
                    donViCell = sheet.Cells[startRowLogo + 1, 1, startRowLogo + 1, 1];
                    donViCell.Merge = true;
                    donViCell.Value = listDonVi.Count > 0 ? listDonVi[0].TenDonVi.ToUpper() : string.Empty;
                    donViCell.Style.Font.Bold = true;
                    donViCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                }
                else
                {
                    donViCell = sheet.Cells[2, 1, 2, 1];
                    donViCell.Merge = true;
                    donViCell.Value = tenNhomDV;
                    donViCell.Style.Font.Bold = true;
                    donViCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                }

                int startRowTile = 2;
                var titleCell = sheet.Cells[startRowTile, 2, startRowTile, listDonVi.Count * 3 + 7];
                titleCell.Merge = true;
                titleCell.Value = "BÁO CÁO TIẾN ĐỘ THỰC HIỆN KẾ HOẠCH NĂM " + namKH;
                titleCell.Style.Font.SetFromFont(new Font("Arial", 12));
                titleCell.Style.Font.Bold = true;
                titleCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                titleCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                #region Thong tin moc kiem soat
                int namkh = 0;
                if (!string.IsNullOrEmpty(namKH))
                {
                    try
                    {
                        namkh = System.Convert.ToInt32(namKH);
                    }
                    catch { }
                }
                MocKiemSoatBO mc = new MocKiemSoatBO();
                MocKiemSoat mks = mc.GetMocKSIsActive(namkh);

                string kyBC = string.Empty;
                if (mks != null)
                {
                    kyBC = mks.MOC_KIEM_SOAT;
                }

                var kyBaoCaoCell = sheet.Cells[startRowTile + 1, 2, startRowTile + 1, listDonVi.Count * 3 + 7];
                kyBaoCaoCell.Merge = true;
                kyBaoCaoCell.Value = "Kỳ báo cáo: " + kyBC;
                kyBaoCaoCell.Style.Font.Bold = true;
                kyBaoCaoCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                kyBaoCaoCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                #endregion

                var cellRange1 = sheet.Cells[startRow, startCol, startRow + 1, startCol];
                cellRange1.Merge = true;
                cellRange1.Value = "Chỉ tiêu";
                cellRange1.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cellRange1.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                cellRange1.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange1.Style.Font.Bold = true;
                ExcelColumn colChiTieu = sheet.Column(startCol);
                colChiTieu.Style.WrapText = true;
                colChiTieu.Width = 60;

                cellRange1 = sheet.Cells[startRow, startCol + 1, startRow + 1, startCol + 1];
                cellRange1.Merge = true;
                cellRange1.Value = "Đơn vị tính";
                cellRange1.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cellRange1.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                cellRange1.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange1.Style.Font.Bold = true;
                ExcelColumn colDonVi = sheet.Column(startCol + 1);
                colDonVi.Style.WrapText = true;
                colDonVi.Width = 15;

                var cellRange2 = sheet.Cells[startRow, startCol + 2, startRow, startCol + 6];
                cellRange2.Merge = true;
                cellRange2.Value = "ĐHQGHN";

                cellRange2 = sheet.Cells[startRow + 1, startCol + 2];
                cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange2.Value = "KH";
                cellRange2 = sheet.Cells[startRow + 1, startCol + 3];
                cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange2.Value = "%";
                cellRange2 = sheet.Cells[startRow + 1, startCol + 4];
                cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange2.Value = "ĐC";
                cellRange2 = sheet.Cells[startRow + 1, startCol + 5];
                cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange2.Value = "%";
                cellRange2 = sheet.Cells[startRow + 1, startCol + 6];
                cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange2.Value = "Tổng TH";

                int colStart = startCol + 7;
                for (int i = 0; i < listDonVi.Count; i++)
                {
                    cellRange2 = sheet.Cells[startRow, colStart, startRow, colStart + 2];
                    cellRange2.Merge = true;
                    cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                    cellRange2.Value = listDonVi[i].TenVietTat;

                    cellRange2 = sheet.Cells[startRow + 1, colStart];
                    cellRange2.Value = "KH";
                    cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);

                    cellRange2 = sheet.Cells[startRow + 1, colStart + 1];
                    cellRange2.Value = "TH";
                    cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);

                    cellRange2 = sheet.Cells[startRow + 1, colStart + 2];
                    cellRange2.Value = "%";
                    cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                    colStart = colStart + 3;
                }
                cellRange2 = sheet.Cells[startRow, startCol + 2, startRow + 1, colStart - 1];
                cellRange2.Style.Font.Bold = true;
                cellRange2.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cellRange2.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);

                int beginRow = startRow + 1;
                int beginCol = startCol;
                for (int i = 0; i < chiTieuChinhThucDHQG.Count; i++)
                {
                    beginRow++;
                    cellRange2 = sheet.Cells[beginRow, startCol];
                    cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                    if (chiTieuChinhThucDHQG[i].CAP == 1)
                    {
                        cellRange2.Value = chiTieuChinhThucDHQG[i].TEN_CHI_TIEU.ToUpper();
                    }
                    else cellRange2.Value = chiTieuChinhThucDHQG[i].TEN_CHI_TIEU;

                    if (chiTieuChinhThucDHQG[i].CAP == 1 ||
                        chiTieuChinhThucDHQG[i].CAP == 2)
                    {
                        cellRange2.Style.Font.Bold = true;
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);

                        //Don vi tinh
                        cellRange2 = sheet.Cells[beginRow, startCol + 1];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);

                        //KH
                        cellRange2 = sheet.Cells[beginRow, startCol + 2];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);

                        //%
                        cellRange2 = sheet.Cells[beginRow, startCol + 3];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);
                        //DC
                        cellRange2 = sheet.Cells[beginRow, startCol + 4];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);
                        //%
                        cellRange2 = sheet.Cells[beginRow, startCol + 5];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);
                        //Tổng TH
                        cellRange2 = sheet.Cells[beginRow, startCol + 6];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);
                    }

                    if (chiTieuChinhThucDHQG[i].CAP > 2)
                    {
                        cellRange2 = sheet.Cells[beginRow, startCol + 1];
                        cellRange2.Value = chiTieuChinhThucDHQG[i].DON_VI_TINH;
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        //KH
                        cellRange2 = sheet.Cells[beginRow, startCol + 2];
                        cellRange2.Value = chiTieuChinhThucDHQG[i].GIA_TRI;
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        //%
                        cellRange2 = sheet.Cells[beginRow, startCol + 3];
                        cellRange2.Value = chiTieuChinhThucDHQG[i].GIA_TRI > 0 ?
                            Math.Round(((float)keHoachThucHienDHQG[i].GIA_TRI / chiTieuChinhThucDHQG[i].GIA_TRI) * 100) : 0;
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        //DC
                        cellRange2 = sheet.Cells[beginRow, startCol + 4];
                        cellRange2.Value = keHoachDieuChinhDHQG[i].GIA_TRI;
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        //%
                        cellRange2 = sheet.Cells[beginRow, startCol + 5];
                        cellRange2.Value = chiTieuChinhThucDHQG[i].GIA_TRI > 0 ?
                            Math.Round(((float)keHoachDieuChinhDHQG[i].GIA_TRI / chiTieuChinhThucDHQG[i].GIA_TRI) * 100) : 0;
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        //Tổng TH
                        cellRange2 = sheet.Cells[beginRow, startCol + 6];
                        cellRange2.Value = chiTieuChinhThucDHQG[i].GIA_TRI > 0 ? keHoachThucHienDHQG[i].GIA_TRI : 0;
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                    }
                }
                cellRange2 = sheet.Cells[startRow + 1, startCol, beginRow, startCol + 1];
                cellRange2.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cellRange2.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;

                colStart = startCol + 7;
                beginRow = startRow + 1;
                for (int i = 0; i < dsChiTieuDonVi.Count; i++)
                {
                    int len = dsChiTieuDonVi[i].Count;
                    beginRow = startRow + 1;
                    for (int j = 0; j < len; j++)
                    {
                        beginRow++;
                        cellRange2 = sheet.Cells[beginRow, colStart];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        if (dsChiTieuDonVi[i][j].CAP > 2)
                        {
                            cellRange2 = sheet.Cells[beginRow, colStart];
                            cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);

                            cellRange2.Value = dsChiTieuDonVi[i][j].GIA_TRI;
                            cellRange2 = sheet.Cells[beginRow, colStart + 1];
                            cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                            cellRange2.Value = dsKHThucHienDonVi[i][j].GIA_TRI;

                            cellRange2 = sheet.Cells[beginRow, colStart + 2];
                            cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                            cellRange2.Value = dsChiTieuDonVi[i][j].GIA_TRI > 0 ?
                                Math.Round(((float)dsKHThucHienDonVi[i][j].GIA_TRI / dsChiTieuDonVi[i][j].GIA_TRI) * 100) : 0;
                        }
                        else
                        {
                            cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                            cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);

                            cellRange2 = sheet.Cells[beginRow, colStart + 1];
                            cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                            cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);
                            cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);

                            cellRange2 = sheet.Cells[beginRow, colStart + 2];
                            cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                            cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);
                            cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        }
                    }
                    colStart = colStart + 3;
                }



                //MemoryStream stream = new MemoryStream();
                //ZipOutputStream zipstream = new ZipOutputStream(stream);
                //zipstream.SetLevel(3); //0-9, 9 being the highest level of compression

                //// loops through the PDFs I need to create
                //var newEntry = new ZipEntry("TinhHinhThucHienKeHoach" + namKH + ".xlsx"); //vanduong, 16/7/2015
                //newEntry.DateTime = DateTime.Now;
                //zipstream.PutNextEntry(newEntry);
                //MemoryStream inStream = new MemoryStream(excelPackage.GetAsByteArray());
                //StreamUtils.Copy(inStream, zipstream, new byte[4096]);
                //inStream.Close();
                //zipstream.CloseEntry();

                //zipstream.IsStreamOwner = false;
                //zipstream.Close();
                //stream.Position = 0;

                //FileResult result = File(stream.ToArray(), "application/octet-stream", "TinhHinhThucHienKeHoach" + namKHId + ".zip");
                FileResult result = File(excelPackage.GetAsByteArray(), "application/octet-stream", "TinhHinhThucHienKeHoach" + namKH + ".xlsx");
                //stream.Dispose();

                return result;

            }


        }

        public FileResult ExportKhungChiTieu(int namKHId)
        {
            ExportExcellBO sqlBO = new ExportExcellBO();
            int laChiTieuChinhThuc = 31;
            int laChiTieuDieuChinh = 61;
            List<ExportExcell> chiTieuChinhThucDHQG = sqlBO.GetListChiTieuDHQG(namKHId, laChiTieuChinhThuc);
            //var keHoachThucHienDHQG = sqlBO.GetListKeHoachThucHienDHQG(namKHId, laChiTieuChinhThuc);
            var keHoachDieuChinhDHQG = sqlBO.GetListChiTieuDHQG(namKHId, laChiTieuDieuChinh);

            List<NhomDonVi> danhSachNhomDonVi = nhomdvbo.GetNhomDVByType(1);

            Dictionary<string, List<DonVi>> danhSachDonViTheoNhom = new Dictionary<string, List<DonVi>>();
            int count = danhSachNhomDonVi.Count;
            for (int i = 0; i < count; i++)
            {
                danhSachDonViTheoNhom.Add(danhSachNhomDonVi[i].TenNhomDonVi,
                    donvibo.GetAllDonViCoBanByNhom(danhSachNhomDonVi[i].NhomDonViId));
            }

            Dictionary<string, List<List<ExportExcell>>> data = new Dictionary<string, List<List<ExportExcell>>>();
            List<List<ExportExcell>> dataTheoDonVi = null;
            List<ExportExcell> dataDonVi = null;
            for (int i = 0; i < count; i++)
            {
                List<DonVi> danhSachDonVi = danhSachDonViTheoNhom[danhSachNhomDonVi[i].TenNhomDonVi];
                dataTheoDonVi = new List<List<ExportExcell>>();
                for (int j = 0; j < danhSachDonVi.Count; j++)
                {
                    dataDonVi = sqlBO.GetListChiTieuDonVi(namKHId, laChiTieuDieuChinh, danhSachDonVi[j].DonViId);
                    if (!HasData(dataDonVi)) // luon lon hon 0, can tinh tong = 0
                    {
                        dataDonVi = sqlBO.GetListChiTieuDonVi(namKHId, laChiTieuChinhThuc, danhSachDonVi[j].DonViId);
                    }
                    dataTheoDonVi.Add(dataDonVi);
                }
                data.Add(danhSachNhomDonVi[i].TenNhomDonVi, dataTheoDonVi);
            }
            List<ExportExcell> donViPhuTrach = sqlBO.GetListBanPhuTrach(namKHId, 0);
            Color backGroundColor = Color.FromArgb(208, 206, 206);
            using (var excelPackage = new ExcelPackage())
            {
                #region header
                excelPackage.Workbook.Properties.Author = "vanduong";
                excelPackage.Workbook.Properties.Title = "Export excel";
                var sheet = excelPackage.Workbook.Worksheets.Add("Tình hình thực hiện kế hoạch " + "2016");
                sheet.PrinterSettings.TopMargin = topMargin;
                sheet.PrinterSettings.BottomMargin = bottomMargin;
                sheet.PrinterSettings.LeftMargin = leftMargin;
                sheet.PrinterSettings.RightMargin = rightMargin;
                int tongSoDonVi = 12; //can lay dong listDonVi.Count
                var printArea = sheet.Cells[1, 1, 1000, tongSoDonVi * 3 + 7];
                printArea.Style.Font.SetFromFont(new Font("Arial", 10));

                // output a line for the headers
                sheet.Name = "Khung chỉ tiêu KH " + namKHId;
                int startRowLogo = 1;
                var donViCell = sheet.Cells[startRowLogo, 1, startRowLogo, 1];
                donViCell.Merge = true;
                donViCell.Value = "ĐẠI HỌC QUỐC GIA HÀ NỘI";
                donViCell.Style.Font.Bold = true;
                donViCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;

                int startRowTile = 2;
                var titleCell = sheet.Cells[startRowTile, 2, startRowTile, tongSoDonVi * 3 + 7];
                titleCell.Merge = true;
                titleCell.Value = "KHUNG CHỈ TIÊU KẾ HOẠCH NĂM " + "2016";//namKH;
                titleCell.Style.Font.SetFromFont(new Font("Arial", 12));
                titleCell.Style.Font.Bold = true;
                titleCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                titleCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                int col = startCol;
                var cellRange1 = sheet.Cells[startRow, col];
                cellRange1.Value = "Chỉ tiêu";
                cellRange1.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cellRange1.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                cellRange1.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange1.Style.Font.Bold = true;
                ExcelColumn colChiTieu = sheet.Column(startCol);
                colChiTieu.Style.WrapText = true;
                colChiTieu.Width = 60;
                col++;
                cellRange1 = sheet.Cells[startRow, col];
                cellRange1.Value = "Đơn vị tính";
                cellRange1.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange1.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cellRange1.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                cellRange1.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange1.Style.Font.Bold = true;
                ExcelColumn colDonVi = sheet.Column(col);
                colDonVi.Style.WrapText = true;
                colDonVi.Width = 15;
                col++;
                cellRange1 = sheet.Cells[startRow, col];
                cellRange1.Value = "Phụ trách";
                cellRange1.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange1.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cellRange1.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                cellRange1.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange1.Style.Font.Bold = true;
                colDonVi = sheet.Column(col);
                colDonVi.Style.WrapText = true;
                colDonVi.Width = 15;

                col++;
                var cellRange2 = sheet.Cells[startRow, col, startRow, col + 1];
                cellRange2.Merge = true;
                cellRange2.Value = "ĐHQGHN";
                cellRange2.Style.Font.Bold = true;
                cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange2.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cellRange2.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                cellRange2 = sheet.Cells[startRow + 1, col];
                cellRange2.Value = "KH";
                cellRange2.Style.Font.Bold = true;
                cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange2.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cellRange2.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                cellRange2 = sheet.Cells[startRow + 1, col + 1];
                cellRange2.Value = "ĐC";
                cellRange2.Style.Font.Bold = true;
                cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange2.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cellRange2.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                col += 2;
                for (int i = 0; i < danhSachNhomDonVi.Count; i++)
                {
                    List<DonVi> donvi = danhSachDonViTheoNhom[danhSachNhomDonVi[i].TenNhomDonVi];
                    int start = col;
                    for (int j = 0; j < donvi.Count; j++)
                    {
                        cellRange2 = sheet.Cells[startRow + 1, col];
                        cellRange2.Value = donvi[j].TenVietTat;
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        cellRange2.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                        cellRange2.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        cellRange2.Style.Font.Bold = true;
                        col++;
                    }
                    cellRange2 = sheet.Cells[startRow, start, startRow, col - 1];
                    cellRange2.Merge = true;
                    cellRange2.Value = danhSachNhomDonVi[i].TenNhomDonVi;
                    cellRange2.Style.Font.Bold = true;
                    cellRange2.Style.WrapText = true;
                    cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                    cellRange2.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    cellRange2.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                }
                #endregion

                int beginRow = startRow + 1;
                int beginCol = startCol;
                for (int i = 0; i < chiTieuChinhThucDHQG.Count; i++)
                {
                    beginRow++;
                    cellRange2 = sheet.Cells[beginRow, startCol];
                    cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                    if (chiTieuChinhThucDHQG[i].CAP == 1)
                    {
                        cellRange2.Value = chiTieuChinhThucDHQG[i].TEN_CHI_TIEU.ToUpper();
                    }
                    else cellRange2.Value = chiTieuChinhThucDHQG[i].TEN_CHI_TIEU;

                    if (chiTieuChinhThucDHQG[i].CAP == 1 ||
                        chiTieuChinhThucDHQG[i].CAP == 2)
                    {
                        cellRange2.Style.Font.Bold = true;
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);

                        //Don vi tinh
                        cellRange2 = sheet.Cells[beginRow, startCol + 1];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);

                        //Phu trach
                        cellRange2 = sheet.Cells[beginRow, startCol + 2];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);
                        if (donViPhuTrach[i].CAP == 2)
                            cellRange2.Value = (donViPhuTrach[i].DON_VI ?? string.Empty);

                        //KH
                        cellRange2 = sheet.Cells[beginRow, startCol + 3];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);
                        //DC
                        cellRange2 = sheet.Cells[beginRow, startCol + 4];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);
                    }

                    if (chiTieuChinhThucDHQG[i].CAP > 2)
                    {
                        cellRange2 = sheet.Cells[beginRow, startCol + 1];
                        cellRange2.Value = chiTieuChinhThucDHQG[i].DON_VI_TINH;
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        //Phu trach
                        cellRange2 = sheet.Cells[beginRow, startCol + 2];
                        //cellRange2.Value = (donViPhuTrach[i].DON_VI ?? string.Empty);
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        //KH
                        cellRange2 = sheet.Cells[beginRow, startCol + 3];
                        cellRange2.Value = chiTieuChinhThucDHQG[i].GIA_TRI;
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        //DC
                        cellRange2 = sheet.Cells[beginRow, startCol + 4];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        cellRange2.Value = keHoachDieuChinhDHQG[i].GIA_TRI;
                    }
                }
                beginCol = startCol + 4;
                beginRow = startRow + 1;
                for (int i = 0; i < danhSachNhomDonVi.Count; i++)
                {
                    dataTheoDonVi = data[danhSachNhomDonVi[i].TenNhomDonVi];
                    for (int j = 0; j < dataTheoDonVi.Count; j++)
                    {
                        beginCol++;
                        beginRow = startRow + 1;
                        for (int k = 0; k < dataTheoDonVi[j].Count; k++)
                        {
                            beginRow++;
                            cellRange2 = sheet.Cells[beginRow, beginCol];
                            cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                            if (dataTheoDonVi[j][k].CAP < 3)
                            {
                                cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                                cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);
                            }
                            else
                            {
                                cellRange2.Value = dataTheoDonVi[j][k].GIA_TRI;
                            }
                        }
                    }
                }

                FileResult result = File(excelPackage.GetAsByteArray(), "application/octet-stream", "KhungChiTieuKeHoach" + "2016" + ".xlsx");

                return result;

            }
        }

        public FileResult OpenPdfData(int namKHId, int nhomDonViId, string namKH, int donViId)
        {
            string tenNhomDV = string.Empty;
            if (nhomDonViId > 0)
            {
                NhomDonViBO ndvObj = new NhomDonViBO();
                NhomDonVi obj = ndvObj.GetById(nhomDonViId);
                if (obj != null) tenNhomDV = "NHÓM " + obj.TenNhomDonVi.ToUpper();
            }

            ExportExcellBO sqlBO = new ExportExcellBO();
            int laChiTieuChinhThuc = 31;
            int laChiTieuDieuChinh = 61;
            List<DonVi> listDonVi = new List<DonVi>();
            if (donViId > 0)
            {
                listDonVi.Add(donvibo.GetById(donViId));
            }
            else listDonVi = donvibo.GetAllDonViByNhom(nhomDonViId);

            List<ExportExcell> chiTieuChinhThucDHQG = sqlBO.GetListChiTieuDHQG_TheoNhom(namKHId, laChiTieuChinhThuc, donViId, nhomDonViId);
            var keHoachThucHienDHQG = sqlBO.GetListKeHoachThucHienDHQG_TheoNhom(namKHId, laChiTieuChinhThuc, donViId, nhomDonViId);
            var keHoachDieuChinhDHQG = sqlBO.GetListChiTieuDHQG_TheoNhom(namKHId, laChiTieuDieuChinh, donViId, nhomDonViId);

            List<List<ExportExcell>> dsChiTieuDonVi = new List<List<ExportExcell>>();
            List<List<ExportExcell>> dsKHThucHienDonVi = new List<List<ExportExcell>>();
            List<ExportExcell> dataDonVi;
            for (int i = 0; i < listDonVi.Count; i++)
            {
                dataDonVi = sqlBO.GetListChiTieuDonVi_TheoNhom(namKHId, laChiTieuDieuChinh, listDonVi[i].DonViId, nhomDonViId);
                if (!HasData(dataDonVi))
                {
                    dataDonVi = sqlBO.GetListChiTieuDonVi_TheoNhom(namKHId, laChiTieuChinhThuc, listDonVi[i].DonViId, nhomDonViId);
                }
                dsChiTieuDonVi.Add(dataDonVi);
                dsKHThucHienDonVi.Add(sqlBO.GetListKeHoachThucHienDonVi_TheoNhom(namKHId, laChiTieuChinhThuc, listDonVi[i].DonViId, nhomDonViId));
            }

            Color backGroundColor = Color.FromArgb(208, 206, 206);
            using (var excelPackage = new ExcelPackage())
            {
                excelPackage.Workbook.Properties.Author = "vanduong";
                excelPackage.Workbook.Properties.Title = "Export excel";
                var sheet = excelPackage.Workbook.Worksheets.Add("Tình hình thực hiện kế hoạch " + namKH);
                #region Set Margion
                sheet.PrinterSettings.TopMargin = topMargin;
                sheet.PrinterSettings.BottomMargin = bottomMargin;
                sheet.PrinterSettings.LeftMargin = leftMargin;
                sheet.PrinterSettings.RightMargin = rightMargin;
                sheet.PrinterSettings.FitToWidth = 1;
                #endregion

                #region Set column width and set text alignment
                sheet.Column(2).Width = dvtColumnWidth;
                for (int i = 3; i < listDonVi.Count * 3 + 8; i++)
                {
                    sheet.Column(i).Width = dvColumnWidth;
                    sheet.Column(i).Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                }
                #endregion

                var printArea = sheet.Cells[1, 1, 1000, listDonVi.Count * 3 + 7];
                printArea.Style.Font.SetFromFont(new Font("Arial", 10));

                // output a line for the headers
                sheet.Name = "Tình hình thực hiện KH " + namKHId;
                int startRowLogo = 1;
                var donViCell = sheet.Cells[startRowLogo, 1, startRowLogo, 1];
                donViCell.Merge = true;
                donViCell.Value = "ĐẠI HỌC QUỐC GIA HÀ NỘI";
                donViCell.Style.Font.Bold = true;
                donViCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                if (donViId > 0)
                {
                    donViCell = sheet.Cells[startRowLogo + 1, 1, startRowLogo + 1, 1];
                    donViCell.Merge = true;
                    donViCell.Value = listDonVi.Count > 0 ? listDonVi[0].TenDonVi.ToUpper() : string.Empty;
                    donViCell.Style.Font.Bold = true;
                    donViCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                }
                else
                {
                    donViCell = sheet.Cells[2, 1, 2, 1];
                    donViCell.Merge = true;
                    donViCell.Value = tenNhomDV;
                    donViCell.Style.Font.Bold = true;
                    donViCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                }

                int startRowTile = 2;
                var titleCell = sheet.Cells[startRowTile, 2, startRowTile, listDonVi.Count * 3 + 7];
                titleCell.Merge = true;
                titleCell.Value = "BÁO CÁO TIẾN ĐỘ THỰC HIỆN KẾ HOẠCH NĂM " + namKH;
                titleCell.Style.Font.SetFromFont(new Font("Arial", 12));
                titleCell.Style.Font.Bold = true;
                titleCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                titleCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                #region Thong tin moc kiem soat
                int namkh = 0;
                if (!string.IsNullOrEmpty(namKH))
                {
                    try
                    {
                        namkh = System.Convert.ToInt32(namKH);
                    }
                    catch { }
                }
                MocKiemSoatBO mc = new MocKiemSoatBO();
                MocKiemSoat mks = mc.GetMocKSIsActive(namkh);

                string kyBC = string.Empty;
                if (mks != null)
                {
                    kyBC = mks.MOC_KIEM_SOAT;
                }

                var kyBaoCaoCell = sheet.Cells[startRowTile + 1, 2, startRowTile + 1, listDonVi.Count * 3 + 7];
                kyBaoCaoCell.Merge = true;
                kyBaoCaoCell.Value = "Kỳ báo cáo: " + kyBC;
                kyBaoCaoCell.Style.Font.Bold = true;
                kyBaoCaoCell.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                kyBaoCaoCell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                #endregion

                var cellRange1 = sheet.Cells[startRow, startCol, startRow + 1, startCol];
                cellRange1.Merge = true;
                cellRange1.Value = "Chỉ tiêu";
                cellRange1.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cellRange1.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                cellRange1.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange1.Style.Font.Bold = true;
                //ExcelColumn colChiTieu = sheet.Column(startCol);
                //colChiTieu.Style.WrapText = true;
                //colChiTieu.Width = 60;

                sheet.Column(startCol).Style.WrapText = true;
                double chitieuWidthMin = 30;
                double cacDonViWidth = (listDonVi.Count * 3 + 5) * dvColumnWidth + dvtColumnWidth;
                double chitieuWidth = portraitSize - cacDonViWidth;
                if (chitieuWidth < chitieuWidthMin)
                {
                    chitieuWidth = Math.Max(chitieuWidthMin, landscapeSize - cacDonViWidth);
                    isLandScape = true;
                }

                sheet.Column(startCol).Width = chitieuWidth;

                cellRange1 = sheet.Cells[startRow, startCol + 1, startRow + 1, startCol + 1];
                cellRange1.Merge = true;
                cellRange1.Value = "Đơn vị tính";
                cellRange1.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cellRange1.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                cellRange1.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange1.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                cellRange1.Style.Font.Bold = true;
                ExcelColumn colDonVi = sheet.Column(startCol + 1);
                colDonVi.Style.WrapText = true;
                colDonVi.Width = 15;

                var cellRange2 = sheet.Cells[startRow, startCol + 2, startRow, startCol + 6];
                cellRange2.Merge = true;
                cellRange2.Value = "ĐHQGHN";

                cellRange2 = sheet.Cells[startRow + 1, startCol + 2];
                cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange2.Value = "KH";
                cellRange2 = sheet.Cells[startRow + 1, startCol + 3];
                cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange2.Value = "%";
                cellRange2 = sheet.Cells[startRow + 1, startCol + 4];
                cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange2.Value = "ĐC";
                cellRange2 = sheet.Cells[startRow + 1, startCol + 5];
                cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange2.Value = "%";
                cellRange2 = sheet.Cells[startRow + 1, startCol + 6];
                cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                cellRange2.Value = "TTH";

                int colStart = startCol + 7;
                for (int i = 0; i < listDonVi.Count; i++)
                {
                    cellRange2 = sheet.Cells[startRow, colStart, startRow, colStart + 2];
                    cellRange2.Merge = true;
                    cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                    cellRange2.Value = listDonVi[i].TenVietTat;

                    cellRange2 = sheet.Cells[startRow + 1, colStart];
                    cellRange2.Value = "KH";
                    cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);

                    cellRange2 = sheet.Cells[startRow + 1, colStart + 1];
                    cellRange2.Value = "TH";
                    cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);

                    cellRange2 = sheet.Cells[startRow + 1, colStart + 2];
                    cellRange2.Value = "%";
                    cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                    colStart = colStart + 3;
                }
                cellRange2 = sheet.Cells[startRow, startCol + 2, startRow + 1, colStart - 1];
                cellRange2.Style.Font.Bold = true;
                cellRange2.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cellRange2.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);

                int beginRow = startRow + 1;
                int beginCol = startCol;
                for (int i = 0; i < chiTieuChinhThucDHQG.Count; i++)
                {
                    beginRow++;
                    cellRange2 = sheet.Cells[beginRow, startCol];
                    cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                    if (chiTieuChinhThucDHQG[i].CAP == 1)
                    {
                        cellRange2.Value = chiTieuChinhThucDHQG[i].TEN_CHI_TIEU.ToUpper();
                    }
                    else cellRange2.Value = chiTieuChinhThucDHQG[i].TEN_CHI_TIEU;

                    if (chiTieuChinhThucDHQG[i].CAP == 1 ||
                        chiTieuChinhThucDHQG[i].CAP == 2)
                    {
                        cellRange2.Style.Font.Bold = true;
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);

                        //Don vi tinh
                        cellRange2 = sheet.Cells[beginRow, startCol + 1];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);

                        //KH
                        cellRange2 = sheet.Cells[beginRow, startCol + 2];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);

                        //%
                        cellRange2 = sheet.Cells[beginRow, startCol + 3];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);
                        //DC
                        cellRange2 = sheet.Cells[beginRow, startCol + 4];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);
                        //%
                        cellRange2 = sheet.Cells[beginRow, startCol + 5];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);
                        //Tổng TH
                        cellRange2 = sheet.Cells[beginRow, startCol + 6];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                        cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);
                    }

                    if (chiTieuChinhThucDHQG[i].CAP > 2)
                    {
                        cellRange2 = sheet.Cells[beginRow, startCol + 1];
                        cellRange2.Value = chiTieuChinhThucDHQG[i].DON_VI_TINH;
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        //KH
                        cellRange2 = sheet.Cells[beginRow, startCol + 2];
                        cellRange2.Value = chiTieuChinhThucDHQG[i].GIA_TRI;
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        //%
                        cellRange2 = sheet.Cells[beginRow, startCol + 3];
                        cellRange2.Value = chiTieuChinhThucDHQG[i].GIA_TRI > 0 ?
                            Math.Round(((float)keHoachThucHienDHQG[i].GIA_TRI / chiTieuChinhThucDHQG[i].GIA_TRI) * 100) : 0;
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        //DC
                        cellRange2 = sheet.Cells[beginRow, startCol + 4];
                        cellRange2.Value = keHoachDieuChinhDHQG[i].GIA_TRI;
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        //%
                        cellRange2 = sheet.Cells[beginRow, startCol + 5];
                        cellRange2.Value = chiTieuChinhThucDHQG[i].GIA_TRI > 0 ?
                            Math.Round(((float)keHoachDieuChinhDHQG[i].GIA_TRI / chiTieuChinhThucDHQG[i].GIA_TRI) * 100) : 0;
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        //Tổng TH
                        cellRange2 = sheet.Cells[beginRow, startCol + 6];
                        cellRange2.Value = chiTieuChinhThucDHQG[i].GIA_TRI > 0 ? keHoachThucHienDHQG[i].GIA_TRI : 0;
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                    }
                }
                cellRange2 = sheet.Cells[startRow + 1, startCol, beginRow, startCol + 1];
                cellRange2.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                cellRange2.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;

                colStart = startCol + 7;
                beginRow = startRow + 1;
                for (int i = 0; i < dsChiTieuDonVi.Count; i++)
                {
                    int len = dsChiTieuDonVi[i].Count;
                    beginRow = startRow + 1;
                    for (int j = 0; j < len; j++)
                    {
                        beginRow++;
                        cellRange2 = sheet.Cells[beginRow, colStart];
                        cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        if (dsChiTieuDonVi[i][j].CAP > 2)
                        {
                            cellRange2 = sheet.Cells[beginRow, colStart];
                            cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);

                            cellRange2.Value = dsChiTieuDonVi[i][j].GIA_TRI;
                            cellRange2 = sheet.Cells[beginRow, colStart + 1];
                            cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                            cellRange2.Value = dsKHThucHienDonVi[i][j].GIA_TRI;

                            cellRange2 = sheet.Cells[beginRow, colStart + 2];
                            cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                            cellRange2.Value = dsChiTieuDonVi[i][j].GIA_TRI > 0 ?
                                Math.Round(((float)dsKHThucHienDonVi[i][j].GIA_TRI / dsChiTieuDonVi[i][j].GIA_TRI) * 100) : 0;
                        }
                        else
                        {
                            cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                            cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);

                            cellRange2 = sheet.Cells[beginRow, colStart + 1];
                            cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                            cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);
                            cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);

                            cellRange2 = sheet.Cells[beginRow, colStart + 2];
                            cellRange2.Style.Fill.PatternType = ExcelFillStyle.Solid;
                            cellRange2.Style.Fill.BackgroundColor.SetColor(backGroundColor);
                            cellRange2.Style.Border.BorderAround(ExcelBorderStyle.Thin);
                        }
                    }
                    colStart = colStart + 3;
                }

                MemoryStream inStream = new MemoryStream(excelPackage.GetAsByteArray());

                //phunx1, 20160422: xu ly open pdf
                XlsFile Result11 = new XlsFile(1, true);

                Result11.Open(inStream);
                Result11.PrintPaperSize = TPaperSize.A3;
                if (!isLandScape) Result11.PrintLandscape = false;
                else Result11.PrintLandscape = true;
                Result11.AutofitRowsOnWorkbook(true, true, 1);

                using (FlexCelPdfExport pdf = new FlexCelPdfExport())
                {
                    pdf.Workbook = Result11;

                    pdf.FontEmbed = FlexCel.Pdf.TFontEmbed.Embed;


                    using (MemoryStream ms = new MemoryStream())
                    {
                        pdf.BeginExport(ms);
                        pdf.ExportAllVisibleSheets(true, "BaoCaoTuan");
                        pdf.EndExport();
                        ms.Position = 0;
                        //return File(ms.ToArray(), "application/pdf");
                        return File(ms.ToArray(), "application/pdf");
                    }
                }

                //inStream.Close();
                inStream.Dispose();
            }

            return null;
        }

        private DataTable GetDataTable()
        {
            DataTable table = new DataTable();
            table.Columns.Add("Chỉ tiêu");
            table.Columns.Add("Mức hoàn thành");
            return table;
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult CheckChotbyMocKS(int makh, int mocks)
        {
            List<NAMDV> lst = new List<NAMDV>();
            MocKiemSoatBO mks = new MocKiemSoatBO();
            lst = mks.CheckChotByMocKS(makh, mocks);
            return Json(lst, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult CheckChotbyMocKSByBan(int namkh)
        {
            List<NAMDV> lst = new List<NAMDV>();
            mockiemsoatbo = new MocKiemSoatBO();
            var mskid = mockiemsoatbo.GetMocKSIsActive(namkh);
            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            mockiemsoatbo = new MocKiemSoatBO();
            lst = mockiemsoatbo.CheckChotByMocKSByBan(namkh, mskid.MOC_KIEM_SOAT_ID, user.listDonVi[0].DonViId);
            return Json(lst, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult CheckChotbyMocKSban(int makh, int mocks)
        {
            MocKiemSoatBO mks = new MocKiemSoatBO();
            var lstnew = mks.CheckBanChotByMocKS(makh, mocks);
            return Json(lstnew, JsonRequestBehavior.AllowGet);
        }

        private List<double> TongThucHien_TheoNhom(int namKHId, int nhomDonViId)
        {
            ExportExcellBO sqlBO = new ExportExcellBO();
            List<DonVi> toanBoDonVi = donvibo.GetAllDonViCoBanByNhom(nhomDonViId);
            List<List<ExportExcell>> dsKHThucHienDonVi = new List<List<ExportExcell>>();
            int laChiTieuChinhThuc = 31;
            int len = toanBoDonVi.Count;
            for (int i = 0; i < len; i++)
            {
                dsKHThucHienDonVi.Add(sqlBO.GetListKeHoachThucHienDonVi_TheoNhom(namKHId, laChiTieuChinhThuc, toanBoDonVi[i].DonViId, nhomDonViId));
            }

            List<double> tongThucHien = new List<double>();
            int count = dsKHThucHienDonVi.Count;
            double tongTungChiTieu = 0;
            len = 0;
            if (dsKHThucHienDonVi.Count > 0)
            {
                len = dsKHThucHienDonVi[0].Count;
            }

            for (int i = 0; i < len; i++)
            {
                tongTungChiTieu = 0;
                for (int j = 0; j < dsKHThucHienDonVi.Count; j++)
                {
                    if (dsKHThucHienDonVi[j][i].CAP == 1 || dsKHThucHienDonVi[j][i].CAP == 2)
                        break;
                    tongTungChiTieu += dsKHThucHienDonVi[j][i].GIA_TRI;
                }
                tongThucHien.Add(tongTungChiTieu);
            }

            return tongThucHien;
        }

        private List<double> TongThucHien(int namKHId)
        {
            ExportExcellBO sqlBO = new ExportExcellBO();
            List<DonVi> toanBoDonVi = donvibo.GetAll();
            List<List<ExportExcell>> dsKHThucHienDonVi = new List<List<ExportExcell>>();
            int laChiTieuChinhThuc = 31;
            int len = toanBoDonVi.Count;
            for (int i = 0; i < len; i++)
            {
                dsKHThucHienDonVi.Add(sqlBO.GetListKeHoachThucHienDonVi(namKHId, laChiTieuChinhThuc, toanBoDonVi[i].DonViId));
            }

            List<double> tongThucHien = new List<double>();
            int count = dsKHThucHienDonVi.Count;
            double tongTungChiTieu = 0;
            len = 0;
            if (dsKHThucHienDonVi.Count > 0)
            {
                len = dsKHThucHienDonVi[0].Count;
            }

            for (int i = 0; i < len; i++)
            {
                tongTungChiTieu = 0;
                for (int j = 0; j < dsKHThucHienDonVi.Count; j++)
                {
                    if (dsKHThucHienDonVi[j][i].CAP == 1 || dsKHThucHienDonVi[j][i].CAP == 2)
                        break;
                    tongTungChiTieu += dsKHThucHienDonVi[j][i].GIA_TRI;
                }
                tongThucHien.Add(tongTungChiTieu);
            }

            return tongThucHien;
        }

        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public JsonResult SaveCommnet(List<DAL.Models.DHQG.Comment> cmt)
        {
            commentbo = new CommentBO();
            if (cmt != null)
            {
                mockiemsoatbo = new MocKiemSoatBO();
                History_CommentBO ht = new History_CommentBO();
                var mskid = mockiemsoatbo.GetMocKSIsActive(cmt[0].NAM_KE_HOACH_ID);
                foreach (var item in cmt)
                {
                    History_Comment hts = new History_Comment();
                    hts.BAN_ID = item.BAN_ID;
                    hts.CHI_TIEU_ID = item.CHI_TIEU_ID;
                    hts.COMMENT_BAN = item.COMMENT_BAN == null ? "" : item.COMMENT_BAN;
                    hts.DON_VI_ID = item.DON_VI_ID;
                    hts.COMMENT_DV = item.COMMENT_DV == null ? "" : item.COMMENT_DV;
                    hts.FLAG = 0;
                    hts.MOC = 2;
                    hts.NAM_KE_HOACH_ID = item.NAM_KE_HOACH_ID;
                    hts.MOC_KIEM_SOAT_ID = mskid.MOC_KIEM_SOAT_ID;
                    ht.Insert(hts);
                    item.MOC_KIEM_SOAT_ID = mskid.MOC_KIEM_SOAT_ID;

                }
            }
            bool kq = commentbo.Save(cmt);
            LogStream.Write<List<DAL.Models.DHQG.Comment>>(GetAccountLogin(), CommonData.AppName, "KehoachnamController",
                "SaveCommnet", cmt, Helper.KH_DHQGHN_CODE);
            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public JsonResult SaveCommnetByBan(List<DAL.Models.DHQG.Comment> cmt)
        {
            commentbo = new CommentBO();
            int kq = 0;
            if (cmt != null)
            {
                mockiemsoatbo = new MocKiemSoatBO();
                var mskid = mockiemsoatbo.GetMocKSIsActive(cmt[0].NAM_KE_HOACH_ID);
                NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
                foreach (var item in cmt)
                {
                    item.MOC_KIEM_SOAT_ID = mskid.MOC_KIEM_SOAT_ID;
                    item.BAN_ID = user.listDonVi[0].DonViId;
                }
                kq = commentbo.InsertGiuanamByBan(cmt[0]);

                LogStream.Write<List<DAL.Models.DHQG.Comment>>(GetAccountLogin(), CommonData.AppName, "KehoachnamController",
                "SaveCommnetByBan", cmt, Helper.KH_DHQGHN_CODE);
            }
            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetallCommentByBan(int namkh, int moc, int flag)
        {
            Response.Cookies["checkpopup"].Value = "2";
            Response.Cookies["checkpopup"].Expires = DateTime.Now.AddDays(1);
            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            mockiemsoatbo = new MocKiemSoatBO();
            var mskid = mockiemsoatbo.GetMocKSIsActive(namkh);
            if (user.listDonVi.Count > 0 && user.listDonVi[0].CoQuyenGiamSat == true)
            {
                commentbo = new CommentBO();
                var lstcomment = commentbo.GetallByBan(user.listDonVi[0].DonViId, namkh, moc, flag, mskid.MOC_KIEM_SOAT_ID);
                return Json(lstcomment, JsonRequestBehavior.AllowGet);
            }
            else
            {
                return Json("err", JsonRequestBehavior.AllowGet);
            }

        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetallGiuanamByDV(int namkh, int moc)
        {
            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            commentbo = new CommentBO();
            var lstcomment = commentbo.GetallGiuanamByDV(user.listDonVi[0].DonViId, namkh, moc);
            return Json(lstcomment, JsonRequestBehavior.AllowGet);


        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetallCommentByDv(int namkh, int moc, int flag)
        {
            Response.Cookies["checkpopup"].Value = "2";
            Response.Cookies["checkpopup"].Expires = DateTime.Now.AddDays(1);
            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            if (user.listDonVi.Count > 0)
            {
                mockiemsoatbo = new MocKiemSoatBO();
                var mkst = mockiemsoatbo.GetMocKSIsActive(namkh);
                commentbo = new CommentBO();
                var lstcomment = commentbo.GetallByDv(user.listDonVi[0].DonViId, namkh, moc, flag, mkst.MOC_KIEM_SOAT_ID, user.NGUOI_DUNG_ID);
                return Json(lstcomment, JsonRequestBehavior.AllowGet);
            }
            else
            {
                return Json("err", JsonRequestBehavior.AllowGet);
            }

        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetallCommentByDvNew(int namkh, int moc, int flag)
        {
            Response.Cookies["checkpopup"].Value = "2";
            Response.Cookies["checkpopup"].Expires = DateTime.Now.AddDays(1);
            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            if (user.listDonVi.Count > 0)
            {
                mockiemsoatbo = new MocKiemSoatBO();
                var mkst = mockiemsoatbo.GetMocKSIsActive(namkh);
                commentbo = new CommentBO();
                if (user.listDonVi[0].DonViId == 30)
                {
                    flag = 4;
                }
                var lstcomment = commentbo.GetallByDv(user.listDonVi[0].DonViId, namkh, moc, flag, mkst.MOC_KIEM_SOAT_ID, user.NGUOI_DUNG_ID);
                return Json(lstcomment, JsonRequestBehavior.AllowGet);
            }
            else
            {
                return Json("err", JsonRequestBehavior.AllowGet);
            }

        }
        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public JsonResult UpdateCommentbyDV(List<DAL.Models.DHQG.Comment> cmt)
        {
            int kq = 0;
            if (cmt != null)
            {
                History_CommentBO ht = new History_CommentBO();
                commentbo = new CommentBO();
                mockiemsoatbo = new MocKiemSoatBO();
                var mkst = mockiemsoatbo.GetMocKSIsActive(cmt[0].NAM_KE_HOACH_ID);
                foreach (var item in cmt)
                {
                    History_Comment hts = new History_Comment();
                    hts.BAN_ID = item.BAN_ID;
                    hts.CHI_TIEU_ID = item.CHI_TIEU_ID;
                    hts.COMMENT_BAN = item.COMMENT_BAN == null ? "" : item.COMMENT_BAN;
                    hts.DON_VI_ID = item.DON_VI_ID;
                    hts.COMMENT_DV = item.COMMENT_DV == null ? "" : item.COMMENT_DV;
                    hts.FLAG = 0;
                    hts.MOC = 2;
                    hts.NAM_KE_HOACH_ID = item.NAM_KE_HOACH_ID;
                    hts.MOC_KIEM_SOAT_ID = mkst.MOC_KIEM_SOAT_ID;
                    ht.Insert(hts);
                    item.COMMENT_DV = item.COMMENT_DV == null ? "" : item.COMMENT_DV;
                    item.MOC_KIEM_SOAT_ID = mkst.MOC_KIEM_SOAT_ID;
                    kq = commentbo.UpdateComment(item);
                    LogStream.Write<List<DAL.Models.DHQG.Comment>>(GetAccountLogin(), CommonData.AppName, "KehoachnamController",
              "UpdateCommentbyDV", cmt, Helper.KH_DHQGHN_CODE);
                }
            }
            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public JsonResult UpdateCommentGiuanambyDV(List<DAL.Models.DHQG.Comment> cmt)
        {
            int kq = 0;
            if (cmt != null)
            {
                commentbo = new CommentBO();
                mockiemsoatbo = new MocKiemSoatBO();
                foreach (var item in cmt)
                {
                    kq = commentbo.UpdateCommentGiuaNambyDV(item);
                }
                LogStream.Write<List<DAL.Models.DHQG.Comment>>(GetAccountLogin(), CommonData.AppName, "KehoachnamController",
              "UpdateCommentGiuanambyDV", cmt, Helper.KH_DHQGHN_CODE);
            }
            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public JsonResult UpdateCommentbyBan(List<DAL.Models.DHQG.Comment> cmt)
        {
            int kq = 0;
            if (cmt != null)
            {
                History_CommentBO ht = new History_CommentBO();
                commentbo = new CommentBO();
                mockiemsoatbo = new MocKiemSoatBO();
                var mkst = mockiemsoatbo.GetMocKSIsActive(cmt[0].NAM_KE_HOACH_ID);
                foreach (var item in cmt)
                {
                    History_Comment hts = new History_Comment();
                    hts.BAN_ID = item.BAN_ID;
                    hts.CHI_TIEU_ID = item.CHI_TIEU_ID;
                    hts.COMMENT_BAN = item.COMMENT_BAN == null ? "" : item.COMMENT_BAN;
                    hts.DON_VI_ID = item.DON_VI_ID;
                    hts.COMMENT_DV = item.COMMENT_DV == null ? "" : item.COMMENT_DV;
                    hts.FLAG = 0;
                    hts.MOC = 2;
                    hts.NAM_KE_HOACH_ID = item.NAM_KE_HOACH_ID;
                    hts.MOC_KIEM_SOAT_ID = mkst.MOC_KIEM_SOAT_ID;
                    ht.Insert(hts);
                    item.MOC_KIEM_SOAT_ID = mkst.MOC_KIEM_SOAT_ID;
                    kq = commentbo.UpdateCommentByBAN(item);
                }

                LogStream.Write<List<DAL.Models.DHQG.Comment>>(GetAccountLogin(), CommonData.AppName, "KehoachnamController",
             "UpdateCommentbyBan", cmt, Helper.KH_DHQGHN_CODE);
            }

            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public JsonResult UpdateCommentApprove(int namkh, int chitieu, int donvi, int moc)
        {
            commentbo = new CommentBO();
            mockiemsoatbo = new MocKiemSoatBO();
            var mkst = mockiemsoatbo.GetMocKSIsActive(namkh);
            int kq = commentbo.UpdateCommentApprove(namkh, chitieu, donvi, moc, mkst.MOC_KIEM_SOAT_ID);

            string jsonData = "{NamKH: " + namkh + ", chitieu: " + chitieu + ", donvi: " + donvi + ", moc: " + moc +
                ", MOC_KIEM_SOAT_ID" + mkst.MOC_KIEM_SOAT_ID + "}";
            LogStream.Write(GetAccountLogin(), CommonData.AppName, "KehoachnamController",
            jsonData, "UpdateCommentApprove", Helper.KH_DHQGHN_CODE);

            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public JsonResult UpdateCommentApproveGiuanam(int namkh, int chitieu, int donvi, int moc, int flag)
        {
            commentbo = new CommentBO();
            if (flag == 2)
            {
                NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
                donvi = user.listDonVi[0].DonViId;
            }
            if (flag == 1)
            {
                NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
                chitieu = user.listDonVi[0].DonViId;
            }
            int kq = commentbo.UpdateCommentApproveGiuaNam(namkh, chitieu, donvi, moc, flag);

            string jsonData = "{NamKH: " + namkh + ", chitieu: " + chitieu + ", donvi: " + donvi + ", moc: " + moc +
               ", flag" + flag + "}";
            LogStream.Write(GetAccountLogin(), CommonData.AppName, "KehoachnamController",
            jsonData, "UpdateCommentApproveGiuanam", Helper.KH_DHQGHN_CODE);

            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult CommentCheckChot(int namkh, int moc, int ban)
        {
            mockiemsoatbo = new MocKiemSoatBO();
            var mkst = mockiemsoatbo.GetMocKSIsActive(namkh);
            commentbo = new CommentBO();
            var lstcheck = commentbo.CommentCheckchot(namkh, moc, ban, mkst.MOC_KIEM_SOAT_ID);
            return Json(lstcheck, JsonRequestBehavior.AllowGet);
        }
        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public JsonResult CommentChot(int namkh)
        {
            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            commentbo = new CommentBO();
            mockiemsoatbo = new MocKiemSoatBO();
            var mkst = mockiemsoatbo.GetMocKSIsActive(namkh);
            //var lstcheck = commentbo.CommentCheckchot(namkh, 3, user.listDonVi[0].DonViId, mkst.MOC_KIEM_SOAT_ID);
            //if (lstcheck.Count > 0)
            //{
            //    return Json('0', JsonRequestBehavior.AllowGet);
            //}
            //else
            //{
            var check = commentbo.CommentChot(namkh, user.listDonVi[0].DonViId, mkst.MOC_KIEM_SOAT_ID);
            string jsonData = "{namKHId: " + namkh + ", donviId: " + user.listDonVi[0].DonViId + ", mocKiemSoatId: " + mkst.MOC_KIEM_SOAT_ID + "}";
            LogStream.Write(GetAccountLogin(), CommonData.AppName, "KehoachnamController", jsonData, "CommentChot", Helper.KH_DHQGHN_CODE);
            return Json(check, JsonRequestBehavior.AllowGet);
            //}

        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult CheckCommnetchot(int namkh)
        {
            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            commentbo = new CommentBO();
            mockiemsoatbo = new MocKiemSoatBO();
            var mkst = mockiemsoatbo.GetMocKSIsActive(namkh);
            var lstcheck = commentbo.CommentCheckchot(namkh, 3, user.listDonVi[0].DonViId, mkst.MOC_KIEM_SOAT_ID);
            if (lstcheck.Count > 0)
            {
                return Json('1', JsonRequestBehavior.AllowGet);
            }
            else
            {
                return Json('0', JsonRequestBehavior.AllowGet);
            }
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult CheckFeedBack(int namkh, int dvid)
        {
            mockiemsoatbo = new MocKiemSoatBO();
            MocKiemSoat mc = mockiemsoatbo.GetMocKSIsActive(namkh);
            var kq = kehoachthuchienbo.CheckFeedBack(namkh, dvid, mc.MOC_KIEM_SOAT_ID);
            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult CommentHuychot(int namkh)
        {
            mockiemsoatbo = new MocKiemSoatBO();
            MocKiemSoat mc = mockiemsoatbo.GetMocKSIsActive(namkh);
            mockiemsoatbo = new MocKiemSoatBO();
            MocKiemSoat mcbefo = mockiemsoatbo.Getmksbefo(namkh, mc.MOC_KIEM_SOAT_ID);
            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            commentbo = new CommentBO();
            var lst = commentbo.CommentHuyCHot(namkh, user.listDonVi[0].DonViId, mc.MOC_KIEM_SOAT_ID, mcbefo.MOC_KIEM_SOAT_ID != null ? mcbefo.MOC_KIEM_SOAT_ID : 0);

            string jsonData = "{namKHId: " + namkh + ", donviId: " + user.listDonVi[0].DonViId +
                ",mc.mocKiemSoatId: " + mc.MOC_KIEM_SOAT_ID + ",  mcbefo.MOC_KIEM_SOAT_ID: " + (mcbefo.MOC_KIEM_SOAT_ID != null ? mcbefo.MOC_KIEM_SOAT_ID : 0) + "}";
            LogStream.Write(GetAccountLogin(), CommonData.AppName, "KehoachnamController", jsonData, "CommentChot", Helper.KH_DHQGHN_CODE);

            return Json(lst, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult CheckChitieubymacap1(int mapcap1)
        {
            var kq = chitieubo.GetChitieubyMacap1(mapcap1);
            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetKhRiengbyDV(int namkh, int status)
        {
            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            var kq = kehoachbo.GetKhRiengbyDV(namkh, user.listDonVi[0].DonViId, status);
            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetKhRiengbyDVbefo(int namkh, int status)
        {
            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            mockiemsoatbo = new MocKiemSoatBO();
            namkh = mockiemsoatbo.GetNamkehoch_Getbefore(namkh);
            var kq = kehoachbo.GetKhRiengbyDV(namkh, user.listDonVi[0].DonViId, status);
            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult Getkehoachthrieng(int namkh)
        {
            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            var kq = kehoachthuchienbo.Getkehoachthrieng(namkh, user.listDonVi[0].DonViId);
            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        private bool HasData(List<ExportExcell> dataDonVi)
        {
            double sum = 0;
            for (int i = 0; i < dataDonVi.Count; i++)
            {
                sum += dataDonVi[i].GIA_TRI;
            }

            return sum > 0 ? true : false;
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult Getdvctbydv(int namkh, int dv)
        {
            var kq = donvichitieubo.GetAllByDV(namkh, dv);
            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult SumCanDoi(int namkh, int status)
        {
            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            var kq = kehoachbo.SumCanDOi(namkh, status, user.listDonVi[0].DonViId);
            return Json(kq, JsonRequestBehavior.AllowGet);
        }


        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public JsonResult DeleteKehoachbyDVCT(List<DAL.Models.DHQG.DONVICHITIEUDELETE> lst)
        {
            if (lst != null)
            {
                foreach (var item in lst)
                {
                    var kq = kehoachbo.DeleteKehoachbyDVCT(item.NAM_KE_HOACH_ID, item.CHI_TIEU_ID, item.DON_VI_ID);
                }

                LogStream.Write<List<DAL.Models.DHQG.DONVICHITIEUDELETE>>(GetAccountLogin(), CommonData.AppName,
                    "KehoachnamController", "DeleteKehoachbyDVCT", lst, Helper.KH_DHQGHN_CODE);
            }
            return Json("sdd", JsonRequestBehavior.AllowGet);
        }

        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult Getlistkhthchitieu(int namkh, int mocks)
        {
            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            var kq = chitieubo.Getlistkhthchitieu(namkh, mocks, user.listDonVi[0].DonViId, user.NGUOI_DUNG_ID);
            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult Getlistkhthchitieudachot(int namkh, int mocks)
        {
            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            var kq = chitieubo.Getlistkhthchitieudachot(namkh, mocks, user.listDonVi[0].DonViId, user.NGUOI_DUNG_ID);
            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult HuyChotKeHoachThucHien(int namkh, int mocks)
        {
            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            mockiemsoatbo = new MocKiemSoatBO();
            var mkst = mockiemsoatbo.GetMocKSIsActive(namkh);
            if (mocks != mkst.MOC_KIEM_SOAT_ID)
            {
                return Json("", JsonRequestBehavior.AllowGet);
            }
            else
            {
                var kq = kehoachthuchienbo.HuyChotKeHoachThucHien(namkh, mocks, user.listDonVi[0].DonViId);

                string jsonData = "{NamKH: " + namkh + ", mocKiemSoat: " + mocks + ", donviid: " + user.listDonVi[0].DonViId + "}";
                LogStream.Write(GetAccountLogin(), CommonData.AppName, "KehoachnamController",
                jsonData, "HuyChotKeHoachThucHien", Helper.KH_DHQGHN_CODE);

                return Json(kq, JsonRequestBehavior.AllowGet);
            }
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult Getmochientai(int namkh)
        {
            mockiemsoatbo = new MocKiemSoatBO();
            var mkst = mockiemsoatbo.GetMocKSIsActive(namkh);
            return Json(mkst, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult Getkehoachbymockiemsoat(int namkh, int nhomdonvi, int flag)
        {
            mockiemsoatbo = new MocKiemSoatBO();
            MocKiemSoat mks = mockiemsoatbo.GetMocKSIsActive(namkh);
            var lstkhth = kehoachthuchienbo.Getkehoachbymockiemsoat(namkh, mks.MOC_KIEM_SOAT_ID, nhomdonvi, flag);
            return Json(lstkhth, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult Getkehoachbymockiemsoatbefo(int namkh, int nhomdonvi, int flag)
        {
            mockiemsoatbo = new MocKiemSoatBO();
            MocKiemSoat mks = mockiemsoatbo.GetMocKSIsActive(namkh);
            mockiemsoatbo = new MocKiemSoatBO();
            MocKiemSoat mks1 = mockiemsoatbo.Getmksbefo(namkh, mks.MOC_KIEM_SOAT_ID);
            var lstkhth = kehoachthuchienbo.Getkehoachbymockiemsoat(namkh, mks1.MOC_KIEM_SOAT_ID, nhomdonvi, flag);
            return Json(lstkhth, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult Getmockiemsoatbynam(int namkh)
        {
            mockiemsoatbo = new MocKiemSoatBO();
            var lst = mockiemsoatbo.GetbyNamkehoach(namkh);
            return Json(lst, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetmockiemsoatbynamUpdate(int namkh)
        {
            mockiemsoatbo = new MocKiemSoatBO();
            var lst = mockiemsoatbo.GetbyNamkehoach(namkh);
            MocKiemSoat mks = mockiemsoatbo.GetMocKSIsActive(namkh);
            List<MocKiemSoat> lstMocks = new List<MocKiemSoat>();
            foreach (MocKiemSoat item in lst) {
                if (item.MOC_KIEM_SOAT_ID <= mks.MOC_KIEM_SOAT_ID) {
                    lstMocks.Add(item);
                }
            }
            return Json(lstMocks, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult Getmockiemsoatactivebynam(int namkh)
        {
            mockiemsoatbo = new MocKiemSoatBO();
            MocKiemSoat mks = mockiemsoatbo.GetMocKSIsActive(namkh);
            return Json(mks, JsonRequestBehavior.AllowGet);
        }
        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public JsonResult UpdateNamkehoach(int namkh, int status)
        {
            var kq = namkehoachbo.NamkehoachUpdate(namkh, status);

            string jsonData = "{NamKHId: " + namkh + ", status: " + status + "}";
            LogStream.Write(GetAccountLogin(), CommonData.AppName, "KehoachnamController",
            jsonData, "HuyChotKeHoachThucHien", Helper.KH_DHQGHN_CODE);

            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public JsonResult ChotDieuChinhGiuaNam(int namkh)
        {
            var kq = kehoachbo.ChotDieuChinhGiuanam(namkh);
            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetallbyDonviGiuaNam(int namkh, int moc)
        {
            commentbo = new CommentBO();
            var kq = commentbo.GetallbyDonviGiuaNam(namkh, moc);
            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetKehoachByNamKeHoach_NhomDV_NhomCT(int namkh, int status, int nhomdv, int nhomct, int flag)
        {
            var kq = kehoachbo.GetKehoachByNamKeHoach_NhomDV_NhomCT(namkh, status, flag, nhomdv, nhomct);
            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public JsonResult UpdateCommentbyKHTC(List<DAL.Models.DHQG.Comment> cmt)
        {
            var kq = -1;
            if (cmt != null)
            {
                commentbo = new CommentBO();
                foreach (var item in cmt)
                {
                    kq = commentbo.UpdateCommentbyKHTC(item);
                }

                LogStream.Write<List<DAL.Models.DHQG.Comment>>(GetAccountLogin(), CommonData.AppName, "KehoachnamController",
                "UpdateCommentbyKHTC", cmt, Helper.KH_DHQGHN_CODE);
            }
            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetallCommentNew(int namkh, int moc, int statusid)
        {
            commentbo = new CommentBO();
            var kq = commentbo.GetallComment(namkh, moc, statusid);
            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public JsonResult UpdateCommentbyKHTC_BAN(List<DAL.Models.DHQG.Comment> cmt)
        {
            var kq = -1;
            if (cmt != null)
            {
                commentbo = new CommentBO();
                foreach (var item in cmt)
                {
                    kq = commentbo.UpdateCommentbyKHTC_BAN(item);
                }

                LogStream.Write<List<DAL.Models.DHQG.Comment>>(GetAccountLogin(), CommonData.AppName, "KehoachnamController",
              "UpdateCommentbyKHTC_BAN", cmt, Helper.KH_DHQGHN_CODE);
            }
            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult Getbanchuaguicomment(int namkh, int moc)
        {
            commentbo = new CommentBO();
            var lst = commentbo.GetlistCommnetBanchuachot(namkh, moc);
            return Json(lst, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetkehoachbyOption(int namkh, int chitieu, int donvi, int status)
        {
            var kq = kehoachbo.GetkehoachbyOption(namkh, chitieu, donvi, status);
            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public JsonResult UpdatekehoachbyOption(int namkh, int chitieu, int donvi, int status, string giatri)
        {
            var kq = kehoachbo.UpdatekehoachbyOption(namkh, chitieu, donvi, status, giatri);

            string jsonData = "{NamKHId: " + namkh + ", chitieu: " + chitieu + ", donvi: " + donvi + ", status: " + status +
                ", giatri: " + giatri + "}";
            LogStream.Write(GetAccountLogin(), CommonData.AppName, "KehoachnamController",
            jsonData, "UpdatekehoachbyOption", Helper.KH_DHQGHN_CODE);

            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetKHTHbyOption(int namkh, int chitieu, int donvi, int mocks)
        {
            var kq = kehoachthuchienbo.GetKHTHbyOption(namkh, mocks, donvi, chitieu);
            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public JsonResult AddChitieubyOption(int namkh, int chitieu, int donvi, string giatri, int ban)
        {
            var kq = kehoachbo.AddChitieubyOption(namkh, chitieu, donvi, giatri, ban);

            string jsonData = "{NamKHId: " + namkh + ", chitieu: " + chitieu + ", donvi: " + donvi + ", giatri: " + giatri + ",ban:" + ban + "}";
            LogStream.Write(GetAccountLogin(), CommonData.AppName, "KehoachnamController",
            jsonData, "AddChitieubyOption", Helper.KH_DHQGHN_CODE);

            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetallHistoryComment(int namkh, int moc, int mocks, int flag, int dv, int ct)
        {
            historycommnet = new History_CommentBO();
            if (mocks == -1)
            {
                mockiemsoatbo = new MocKiemSoatBO();
                var mskid = mockiemsoatbo.GetMocKSIsActive(namkh);
                mocks = mskid.MOC_KIEM_SOAT_ID;
            }
            var kq = historycommnet.Getallhstory(namkh, moc, mocks, flag, dv, ct);
            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult CloseOpenNamkh(int namkhid, int flag)
        {
            var namkh = namkehoachbo.GetById(namkhid);
            var kq = -1;
            if (flag == 0)
            {
                if (namkh != null && namkh.STATUS_ID == 50)
                {
                    namkh.STATUS_ID = 60;
                    namkehoachbo = new NamKeHoachBO();
                    kq = namkehoachbo.Save(namkh);

                }
            }
            else
            {
                if (namkh != null && namkh.STATUS_ID == 60)
                {
                    namkh.STATUS_ID = 50;
                    namkehoachbo = new NamKeHoachBO();
                    kq = namkehoachbo.Save(namkh);

                }
            }

            LogStream.Write<DAL.Models.DHQG.NamKeHoach>(GetAccountLogin(), CommonData.AppName,
                "KehoachnamController", "CloseOpenNamKh", namkh, Helper.KH_DHQGHN_CODE);

            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public JsonResult ShareFileSave()
        {
            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            var kq = false;
            var svb = Request.Form["sovanban"];
            var ty = Request.Form["trichyeu"];
            DAL.Models.DHQG.ShareFile sharefile = new DAL.Models.DHQG.ShareFile();
            sharefile.SO_VAN_BAN = svb;
            sharefile.DON_VI_BAN_HANH = user.listDonVi[0].DonViId;
            sharefile.TRICH_YEU = ty;
            for (int i = 0; i < Request.Files.Count; i++)
            {
                var file = Request.Files[i];

                var fileName = Path.GetFileName(file.FileName);

                var path = Path.Combine(Server.MapPath("~/ShareFile/"), fileName);
                sharefile.DUONG_DAN_FILE = fileName;
                file.SaveAs(path);
            }
            sharefile.NGUOI_TAO = user.TAI_KHOAN;
            sharefilebo = new ShareFileBO();

            kq = sharefilebo.Save(sharefile);

            LogStream.Write<ShareFile>(GetAccountLogin(), CommonData.AppName,
                "KehoachnamController", "ShareFileSave", sharefile, Helper.KH_DHQGHN_CODE);

            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public JsonResult ShareFileUpdate()
        {
            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            var kq = false;
            var svb = Request.Form["sovanban"];
            var ty = Request.Form["trichyeu"];
            var vanbanid = Request.Form["vanbanid"];
            DAL.Models.DHQG.ShareFile sharefile = new DAL.Models.DHQG.ShareFile();
            sharefile.VAN_BAN_ID = vanbanid == null ? 0 : int.Parse(vanbanid);
            sharefile.SO_VAN_BAN = svb;
            sharefile.DON_VI_BAN_HANH = user.listDonVi[0].DonViId;
            sharefile.TRICH_YEU = ty;
            string fnam = "";
            for (int i = 0; i < Request.Files.Count; i++)
            {
                var file = Request.Files[i];

                var fileName = Path.GetFileName(file.FileName);

                var path = Path.Combine(Server.MapPath("~/ShareFile/"), fileName);
                fnam = fileName;
                file.SaveAs(path);
            }

            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetShareFilebyUser()
        {
            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            sharefilebo = new ShareFileBO();
            var kq = sharefilebo.GetallbyDonviID(user.listDonVi[0].DonViId);
            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        //[HttpGet]
        //[AjaxValidateAntiForgeryToken]
        //public JsonResult GetShareFilebyUserSearch(string svb, string ngay)
        //{
        //    //NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
        //    //sharefilebo = new ShareFileBO();
        //    //var kq = sharefilebo.GetallbyDonviID(user.listDonVi[0].DonViId).ToList();
        //    //if (svb != "")
        //    //{
        //    //    kq = kq.Where(o => o.SO_VAN_BAN=="gggggg");
        //    //}
        //    //return Json(kq, JsonRequestBehavior.AllowGet);
        //}
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetAllDV()
        {
            DonViBO bo = new DonViBO();
            var tempListDV = bo.GetAll();
            return Json(tempListDV, JsonRequestBehavior.AllowGet);
        }
        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public JsonResult SavePhanQuyen(List<DAL.Models.DHQG.PhanQuyenVanBan> lst)
        {
            var kq = false;
            if (lst != null)
            {
                phaquyenbo = new PhanQuyenVanBanBO();
                var delete = phaquyenbo.DeletebyVanban(lst[0].VAN_BAN_ID);
                LogStream.Write(GetAccountLogin(), CommonData.AppName,
                    "NamkehoachController", "{VanBanId: " + lst[0].VAN_BAN_ID + "}", "SavePhanQuyen", Helper.KH_DHQGHN_CODE);
                phaquyenbo = new PhanQuyenVanBanBO();
                kq = phaquyenbo.Save(lst);
                LogStream.Write<List<DAL.Models.DHQG.PhanQuyenVanBan>>(GetAccountLogin(), CommonData.AppName,
                    "NamkehoachController", "phaquyenbo.DeletebyVanban", lst, Helper.KH_DHQGHN_CODE);
            }
            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetPhanquynbyVanban(string vanbanid)
        {
            List<DAL.Models.DHQG.PhanQuyenVanBan> lst = new List<PhanQuyenVanBan>();
            if (vanbanid != "" && vanbanid != null)
            {
                phaquyenbo = new PhanQuyenVanBanBO();
                lst = phaquyenbo.GetallbyVanban(int.Parse(vanbanid));
            }
            return Json(lst, JsonRequestBehavior.AllowGet);
        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetShareFilebyId(string id)
        {
            int it = 0;
            if (id != "" && id != null)
            {
                it = int.Parse(id);
            }
            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            sharefilebo = new ShareFileBO();
            var kq = sharefilebo.GetallbyID(it);
            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        [HttpPost]
        [AjaxValidateAntiForgeryToken]
        public JsonResult DeleteShareFile(string id)
        {
            ShareFile shf = new ShareFile();
            if (id != "" && id != null)
            {
                sharefilebo = new ShareFileBO();
                shf = sharefilebo.GetallbyID(int.Parse(id))[0];
            }
            shf.DELETED = 1;
            var kq = sharefilebo.Update(shf);

            LogStream.Write<ShareFile>(GetAccountLogin(), CommonData.AppName, "NamkehoachController",
                "DeleteShareFile", shf, Helper.KH_DHQGHN_CODE);

            return Json(kq, JsonRequestBehavior.AllowGet);

        }
        [HttpGet]
        [AjaxValidateAntiForgeryToken]
        public JsonResult GetShareFilebyDonvitruycap()
        {
            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            sharefilebo = new ShareFileBO();
            var kq = sharefilebo.GetallbyDonviTruyCap(user.listDonVi[0].DonViId);
            return Json(kq, JsonRequestBehavior.AllowGet);
        }
        public FileResult Downloadfile(string path)
        {
            NguoiDungModel user = (NguoiDungModel)Session["NguoiDungInfo"];
            try
            {
                ShareFile shf = new ShareFile();
                if (path != "" && path != null)
                {
                    bool check = false;
                    sharefilebo = new ShareFileBO();
                    var kq = sharefilebo.GetallbyDonviTruyCap(user.listDonVi[0].DonViId);
                    foreach (var item in kq)
                    {
                        if (item.VAN_BAN_ID == int.Parse(path))
                        {
                            check = true;
                            break;
                        }
                    }
                    var kq2 = sharefilebo.GetallbyDonviID(user.listDonVi[0].DonViId);
                    foreach (var item in kq2)
                    {
                        if (item.VAN_BAN_ID == int.Parse(path))
                        {
                            check = true;
                            break;
                        }
                    }
                    if (check)
                    {
                        sharefilebo = new ShareFileBO();
                        shf = sharefilebo.GetallbyID(int.Parse(path))[0];
                    }
                }
                var url = Path.Combine(Server.MapPath("~/ShareFile/"), shf.DUONG_DAN_FILE);
                byte[] fileBytes = System.IO.File.ReadAllBytes(@url);
                return File(fileBytes, System.Net.Mime.MediaTypeNames.Application.Octet, shf.DUONG_DAN_FILE);
            }
            catch
            {
                throw;
            }
        }
    }
}
